[發問] 路徑與檔名

PJChen

大大好,

請問:
1. 以下路徑的➤日班理貨換算表,在VBA中路徑把➤變為?而無法開啟檔案,要如何修改程式碼 or 語法?
2. 在Workbooks("OK.xlsx")程式中,其檔案名稱是OK-  5月.xlsx
但檔案因為是共用的,會有人去修改檔名,VBA會找不到檔案,如果我想以"OK.xlsx"去找尋檔案,也就是把"-  5月"省略,請問語法要怎麼寫?

1. Dim mFile As String, 來源檔 As Workbook, 目的檔 As Workbook, Sh As Worksheet, xSh As Worksheet, i As String
   
2. mFile = Dir("D:\蘆竹共用\倉儲共用\?日班理貨換算表\108.5月\OK*.XLSX") '開啟指定名稱開頭的檔案
   
3. Do While mFile <> ""
   
4. Workbooks.Open Filename:="D:\蘆竹共用\倉儲共用\?日班理貨換算表\108.5月\" & mFile
   
5. mFile = Dir()
   
6. Loop
   
7. 
   
8. Set 來源檔 = Workbooks("理貨單_All.xlsx")
   
9. Set Sh = 來源檔.Sheets("比菲多.OK")
   
10.     Set 目的檔 = Workbooks("OK.xlsx")
    
11.     Set xSh = 目的檔.Sheets(i)

複製代碼

ikboy

1. Sub zz()
   
2. Dim mFile As String, 來源檔 As Workbook, 目的檔 As Workbook, Sh As Worksheet, xSh As Worksheet, i As String
   
3. Dim MyPath
   
4. With Application.FileDialog(msoFileDialogFolderPicker)
   
5.     .InitialFileName = "D:\蘆竹共用\倉儲共用"
   
6.     .Title = "日班理貨換算表"
   
7.     .Show
   
8.     If .SelectedItems.Count Then MyPath = .SelectedItems(1) Else Exit Sub
   
9. End With
   
10. mFile = Dir(MyPath & "\" & "OK*.XLSX") '開啟指定名稱開頭的檔案
    
11. Do While mFile <> ""
    
12. Workbooks.Open Filename:=MyPath & "\" & mFile
    
13. mFile = Dir()
    
14. Loop
    
15. Set 來源檔 = Workbooks("理貨單_All.xlsx")
    
16. Set Sh = 來源檔.Sheets("比菲多.OK")
    
17.     Set 目的檔 = Workbooks("OK.xlsx")
    
18.     Set xSh = 目的檔.Sheets(i)
    
19. End Sub

複製代碼

PJChen

回復 2# ikboy

您好,

完整路徑是這樣
"D:\蘆竹共用\倉儲共用\?日班理貨換算表\108.5月\OK*.XLSX"
但某些字出不來,變成?
而 OK*.XLSX我也希望能用模糊字串去代替精確的檔名,以防有人修改檔名時打不開
測試的結果,只能開啟"倉儲共用"的對話框,其他更下一層的子資料夾及檔案都無法開啟！

PJChen

回復 2# ikboy

謝謝您,
我試了一整晚,終於可以用了,路徑還是無法用*取代,但總算可以打開檔案.

1.     Dim mFile As String, mF As String, 來源檔 As Workbook, 目的檔 As Workbook, Sh As Worksheet, xSh As Worksheet, i As String
   
2. 
   
3. Path = "D:\蘆竹共用\倉儲共用\全台理貨換算表\"    '來源資料夾"
   
4.     mFile = Dir(Path & "*萊爾富*.XLSX")          '來源檔名
   
5.         Do While mFile <> ""
   
6.             Application.DisplayAlerts = False       '一般提警示訊息關閉
   
7.             Workbooks.Open Filename:=Path & mFile
   
8.             mFile = Dir
   
9.         Loop
   
10. 
    
11. i = Format(Date, "D") + 1  '以日期設定作為工作表的變數
    
12. '**********************
    
13. Set 來源檔 = Workbooks("理貨單_All.xlsx")
    
14. Set Sh = 來源檔.Sheets("比菲多.萊爾富")
    
15.     mF = Dir(Path & "*五股萊*.XLSX")
    
16.     mF = Dir
    
17.     Set 目的檔 = Workbooks(Dir(Path & "*五股萊*.XLSX")) '模糊檔名
    
18.     Set xSh = 目的檔.Sheets(i)

複製代碼