取唯一,最后一笔数值

limonet

改一下STRSQL

limonet

Sub limonet()
    Dim Cn As Object, StrSQL$
    Set Cn = CreateObject("Adodb.Connection")
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\SHIPP.xls"
    StrSQL = "Select 選項號碼 As 選項號碼,Last(料號) As 料號,Last(品名) As 品名 From [石中$E:K] Group By [選項號碼]"
    StrSQL = "Select 料號,品名 From [Excel 12.0;DataBase=" & ThisWorkbook.FullName & "].[Sheet1$G:G]a Left Join (" & StrSQL & ")b On a.選項號碼=b.選項號碼"
    Range("H2").CopyFromRecordset Cn.Execute(StrSQL)
End Sub

cmo9020

limonet 发表于 2024-3-30 09:33
Sub limonet()
    Dim Cn As Object, StrSQL$
    Set Cn = CreateObject("Adodb.Connection")

谢谢大哥...
我的是修成SELECT a.*, b.料号,b.品名 FROM...
看起来你的比较简洁又方便,谢谢

limonet

cmo9020 发表于 2024-3-30 10:27
谢谢大哥...
我的是修成SELECT a.*, b.料号,b.品名 FROM...
看起来你的比较简洁又方便,谢谢

你加了a与b，更保险。

cmo9020

limonet 发表于 2024-3-30 10:30
你加了a与b，更保险。

大哥...
剩下一个问题请教一下
同时查询读取2个档案簿数据
1.SHIPP
2.scrap
试了很多次,当在执行读取第2个scrap工作簿数据时,它会把第1个的查询结果清空...
请问这要怎么解决?谢谢大哥指导一下~

limonet.rar (31.76 KB, 下载次数: 4)

2024-3-30 21:05 上传

点击文件名下载附件

shiruiqiang

这几天还在学，试试：
Range("H"& range("h65536").end(xlup).row+1).CopyFromRecordset Cn.Execute(StrSQL)

shiruiqiang

cmo9020 发表于 2024-3-30 21:06
大哥...
剩下一个问题请教一下
同时查询读取2个档案簿数据

近期努力在学sql

cmo9020

shiruiqiang 发表于 2024-3-30 21:49
近期努力在学sql

大哥~我是指同时读取2个档案里面数据
SHIPP.xls+scrap.xls

下面代码执行后他会覆盖到第1个读取变空白
试了很多方法...弄不出来

Sub limonet()
    Dim Cn1 As Object, Cn2 As Object, StrSQL$
    Set Cn1 = CreateObject("Adodb.Connection")
    Cn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\SHIPP.xls"
    Set Cn2 = CreateObject("Adodb.Connection")
    Cn2.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.Path & "\scrap.xls"

    StrSQL = "Select 选项号码 As 选项号码, Last(料号) As 料号, Last(品名) As 品名 From [石中$E:K] Group By [选项号码]"
    StrSQL1 = "Select 料号, 品名 From [Excel 12.0;DataBase=" & ThisWorkbook.FullName & "].[Sheet1$G:G]a Left Join (" & StrSQL & ")b On a.选项号码=b.选项号码"
    StrSQL2 = "Select 料号, 品名 From [Excel 12.0;DataBase=" & ThisWorkbook.FullName & "].[Sheet1$G:G]a Left Join (" & StrSQL & ")b On a.选项号码=b.选项号码"

    Range("H2").CopyFromRecordset Cn1.Execute(StrSQL1)
    Range("H2").CopyFromRecordset Cn2.Execute(StrSQL2)
    Cn1.Close
    Cn2.Close
End Sub

limonet

关键字：union all

limonet

更多文件也可以
Sub limonet()
    Dim Cn As Object, StrSQL$, Path$, FileName$
    Set Cn = CreateObject("ADODB.Connection")
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
    Path = ThisWorkbook.Path & "\"
    FileName = Dir(Path & "*.xls")
    Do While FileName <> ""
        If FileName <> "Limonet.xlsm" Then
            StrSQL = StrSQL & " Union All Select 選項號碼 As 選項號碼,Last(料號) As 料號,Last(品名) As 品名 From [Excel 12.0;DataBase=" & Path & FileName & "].[石中$E:K] Group By [選項號碼]"
        End If
        FileName = Dir
    Loop
    StrSQL = "Select 料號,品名 From [Sheet1$G:G]a Left Join (" & Mid(StrSQL, 12) & ")b On a.選項號碼=b.選項號碼"
    Range("H2").CopyFromRecordset Cn.Execute(StrSQL)
End Sub