singo1232001

回復 29# shuo1125


    Xl0000108_TESTv01z04.zip (677.65 KB)

下載次數: 3

2023-12-20 23:41

av ax az 異常  我忘了綁關鍵字 重傳

singo1232001

回復 31# shuo1125


    Xl0000108_TESTv01z05.zip (680.51 KB)

下載次數: 4

2023-12-21 01:59

也可以參考多行放工作表版 SQL語法全部改放工作表 的寫法

Sub 查放工作表精簡版()  '可搭配 表"SQL GPT4prompt" 跟 "SQL" 工作表問GPT放語法
i = Split("Provider=Microsoft.,Jet.OLEDB.4,.0;Extended Properties=Excel ,8,.0;Data Source=", ",")
If Application.Version > 12 Then i(1) = "ACE.OLEDB.12": i(3) = 12
Set cn = CreateObject("adodb.connection"): cn.Open Join(i, "") & ThisWorkbook.FullName

Set sj = Sheets("交易明細2"): Set SQ = Sheets("SQL")
For i = 8 To 40 Step 16
Set s = Sheets(sj.Cells(i - 2, "B").Value)
E1 = "'%" & sj.Cells(i - 2, "E") & "%'"
B1 = "[" & sj.Cells(i - 2, "B") & "$A1:T]  "
    For j = 1 To 9
    sj.Cells(i, SQ.Cells(j, 3).Value).Resize(12, 1) = 0
    repSQL = Replace(Replace(SQ.Cells(j, 2), "'%GN%'", E1), "[M明細帳$A1:T]", B1)
    sj.Cells(i, SQ.Cells(j, 3).Value).CopyFromRecordset cn.Execute(repSQL)
    Next
Next
End Sub