Qin

回復 28# Kubi

在此之前, 我一真都在用你給我的程序碼(10萬筆搜尋)
它的確很快又很好用
只是我想把 "Data" (資料庫) & "Search" (搜尋)這2個檔分開用.
所以我很想知道, 你這篇程式碼的這整句語法是要如何寫的?
我想不同的語法是不是會有不同的寫法.
我只是想拿個參考…


Kubi 大大,謝謝你…

Kubi

回復 71# Qin

自從准大熱心幫忙後，我就沒有再follow此題了。
至於 "Data" (資料庫) & "Search" (搜尋)這2個檔分開用，意思是將Data(資料庫)拆解至另外1個檔案嗎？
若是如此的話，我的寫法可能會開啟Search(搜尋)這個檔的時候，順便讀入Data(資料庫)至暫存工作表，作為搜尋依據。

Qin

回復 72# Kubi

對,就是要分開2個檔.
你可以再幫我一次
讓我知道要怎樣寫嗎?
謝謝!!

Kubi

回復 73# Qin
壓縮檔內有下列兩個檔案：
1.主程式檔案：資料搜尋.xlsm
2.資料庫檔案：SearchData.xlsx
兩個檔案必須放在同個資料夾中。
資料庫檔案名稱必須為SearchData.xlsx
資料搜尋.rar (38.76 KB)

下載次數: 17

2018-10-21 21:34

Qin

回復 74# Kubi

謝謝了...

Andy2483

回復 67# 准提部林

謝謝前輩常用不同方式的程式碼讓後輩學習
1.Application.Goto ['Qty on Hand'!A2]  同  Sheets("Qty on Hand").Activate: [A2].Activate
2.[~!~]的陳述方式會因為 Qty on Hand 之間有空格而無法辨識,所以要以單引號前後包住

以上心得若有錯,請前輩再指導!

singo1232001

感謝原PO 感謝各位大大  
這題很不錯
練習完畢 附上檔案

開啟"SQL搜尋"工作表
幾個簡易功能說明  
1.只在列7輸入 會模糊搜索
2.列6列7都輸入 會區間搜索
3.D,E,G欄 各為文字模糊搜索 可空格 例如:A 司 輸出 A公司
4.全部空白 為全頁搜索
5.A~J欄 同時輸入 會and搜索

限制
1.搜尋檔案 與 檔案來源 路徑目前沒有優化  暫定要在同一資料夾底下
2.很多小bug 只有做主體幾個大功能 過細的客製化功能與需求 尚未製作

Sub 關鍵字查詢()
With CreateObject("adodb.connection"): V = Application.Version
If V >= 12 Then V = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0; "
If V < 12 Then V = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0; "
.Open V & "Data Source=" & ThisWorkbook.Path & "\SearchData.xlsx"

'excel 調用adodb 用 sql 時 欄位名稱有"."符號 須改為 "#"號
If Cells(7, 1) <> "" And Cells(6, 1) = "" Then sq = sq & " and  [No#] like  '%" & Replace(Cells(7, 1), " ", "%") & "%'"
If Cells(7, 1) <> "" And Cells(6, 1) <> "" Then sq = sq & " and  [No#] between " & Cells(6, 1) & " and " & Cells(7, 1) ' & "'"
If Cells(7, 2) <> "" And Cells(6, 2) = "" Then sq = sq & " and  [Inv#]  like '%" & Replace(Cells(7, 2), " ", "%") & "%'"
If Cells(7, 2) <> "" And Cells(6, 2) <> "" Then sq = sq & " and  [Inv#]  between '" & Cells(6, 2) & "' and '" & Cells(7, 2) & "'"
If IsDate(Cells(7, 3)) Then
If Cells(7, 3) <> "" And Cells(6, 3) = "" Then sq = sq & " and  [Date]  like '%" & Cells(7, 3) & "%'"
If Cells(7, 3) <> "" And Cells(6, 3) <> "" Then sq = sq & " and  Format(Date, 'yyyy-MM-dd')  between '" & Format(Cells(6, 3), "yyyy-MM-dd") & "' and '" & Format(Cells(7, 3), "yyyy-MM-dd") & "'"
End If
If Cells(7, 4) <> "" Then sq = sq & " and  [Supplier] like '%" & Replace(Cells(7, 4), " ", "%") & "%'"
If Cells(7, 5) <> "" Then sq = sq & " and  [Inv#(1)] like '%" & Replace(Cells(7, 5), " ", "%") & "%'"
If Cells(7, 6) <> "" And Cells(6, 6) = "" Then sq = sq & " and  [Part No#] like '%" & Replace(Cells(7, 6), " ", "%") & "%'"
If Cells(7, 6) <> "" And Cells(6, 6) <> "" Then sq = sq & " and  [Part No#]  between '" & Cells(6, 6) & "' and '" & Cells(7, 6) & "'"
If Cells(7, 7) <> "" Then sq = sq & " and  [Prod# Name] like '%" & Replace(Cells(7, 7), " ", "%") & "%'"
If Cells(7, 8) <> "" And Cells(6, 8) = "" Then sq = sq & " and  [Qty]  like '%" & Replace(Cells(7, 8), " ", "%") & "%'"
If Cells(7, 8) <> "" And Cells(6, 8) <> "" Then sq = sq & " and  [Qty]  between " & Cells(6, 8) & " and " & Cells(7, 8)
If Cells(7, 9) <> "" And Cells(6, 9) = "" Then sq = sq & " and  [Amt#]  like '%" & Replace(Cells(7, 9), " ", "%") & "%'"
If Cells(7, 9) <> "" And Cells(6, 9) <> "" Then sq = sq & " and  [Amt#]  between " & Cells(6, 9) & " and " & Cells(7, 9)
If Cells(7, 10) <> "" And Cells(6, 10) = "" Then sq = sq & " and  [Total]  like '%" & Replace(Cells(7, 10), " ", "%") & "%'"
If Cells(7, 10) <> "" And Cells(6, 10) <> "" Then sq = sq & " and  [Total]  between " & Cells(6, 10) & " and " & Cells(7, 10)

If sq <> "" Then sq = Mid(sq, 5, 99999)
If sq <> "" Then sq = "select * from [Data$A1:J] where " & sq
If sq = "" Then sq = "select * from [Data$A1:J] "
Sheets("SQL搜尋").Cells(9, 1).Resize(10000, 10).ClearContents
Sheets("SQL搜尋").Cells(9, 1).CopyFromRecordset .Execute(sq)
.Close: End With
End Sub

Sub 清除關鍵字()
Sheets("SQL搜尋").Range("a6:J7").ClearContents
End Sub