Dim T$, B$, i%
T = "\\shl-group.com\dept\MFMG\B.各組資料 (Team inform)\E.生管組 (PPC group)\X.自動化工具(勿刪)\模具異動-六福(急件1.9專用)\"
For i = 0 To 10
B = Format(Date - i, "yyyy.mm.dd")
If Dir(T & B & " 每日模具異動.xlsx") <> Empty Then GoTo i01
Next
MsgBox "找不到 " & Date & " ~ " & Date - 10 & " 檔案": Exit Sub
i01: Workbooks.Open filename:=T & B & " 每日模具異動.xlsx", ReadOnly:=True作者: Andy2483 時間: 2023-4-25 11:08
Dim T$, B$, i%
T = "\\shl-group.com\dept\MFMG\B.各組資料 (Team inform)\E.生管組 (PPC group)\X.自動化工具(勿刪)\模具異動-六福(急件1.9專用)\"
For i = 0 To 10
B = Format(Date - i, "yyyy.mm.dd")
If Dir(T & B & " 每日模具異動.xlsx") <> Empty Then GoTo i01
If Dir(T & B & "每日模具異動.xlsx") <> Empty Then GoTo i02
Next
MsgBox "找不到 " & Date & " ~ " & Date - 10 & " 檔案": Exit Sub
i01: Workbooks.Open filename:=T & B & " 每日模具異動.xlsx", ReadOnly:=True:Exit Sub
i02: Workbooks.Open filename:=T & B & "每日模具異動.xlsx", ReadOnly:=True作者: cowww 時間: 2023-4-25 11:43
Dim T$, B$, i%, xBook as workbook
T = "\\shl-group.com\dept\MFMG\B.各組資料 (Team inform)\E.生管組 (PPC group)\X.自動化工具(勿刪)\模具異動-六福(急件1.9專用)\"
For i = 0 To 10
B = Format(Date - i, "yyyy.mm.dd")
If Dir(T & B & " 每日模具異動.xlsx") <> "" Then
set xBook=Workbooks.Open(T & B & " 每日模具異動.xlsx", ReadOnly:=True)
exit for
Next
if xbook is nothing then MsgBox "找不到目標檔案": Exit Sub
xbook.sheets("LF").Columns("A:AG").Cells.Copy
~~do something ~~
xbook.Close 0作者: Andy2483 時間: 2023-4-26 07:46
Option Explicit
Sub TEST()
Dim T$, B$, i%, xBook As Workbook
'↑宣告變數:(T,B)是字串變數,i是短整數,xBook是活頁簿變數
T = "\\shl-group.com\dept\MFMG\B.各組資料 (Team inform)\E.生管組 (PPC group)\X.自動化工具(勿刪)\模具異動-六福(急件1.9專用)\"
'↑令T這字串變數是 路徑字串
For i = 0 To 10
'↑設順迴圈!i從0到 10
B = Format(Date - i, "yyyy.mm.dd")
'↑令B這字串變數是 今天日期-i變數後,
'轉換為4碼西元年,2碼月,2碼日,中間點間隔的字串
If Dir(T & B & " 每日模具異動.xlsx") <> "" Then
'↑如果T變數連接B變數,再連接" 每日模具異動.xlsx"組成新字串查到這檔案?
Set xBook = Workbooks.Open(T & B & " 每日模具異動.xlsx", ReadOnly:=True)
'↑令xBook這活頁簿變數是被打開的,
'被打開的T變數連接B變數,再連接" 每日模具異動.xlsx"組成新字串的檔案
Exit For
'↑結束迴圈
End If
Next
If xBook Is Nothing Then MsgBox "找不到目標檔案": Exit Sub
'↑如果xBook變數是沒有載入物件!就跳出提示窗,結束程式執行
xBook.Sheets("LF").Columns("A:AG").Cells.Copy
'↑繼續以變數xBook指向目標可以讓程式簡潔易視讀
'~~do something ~~
xBook.Close 0
'↑令xBook不存檔 關閉
End Sub作者: cowww 時間: 2023-4-26 08:57
我終於看懂准提部林大大提供的語法了
以下是我的認知，如有錯誤還請各位大大指教
這段語法是簡化後面"Cells.Copy" & "Close"的寫法
前面將不同的檔名(不管"每日模具異動.xlsx"前面有沒有空格或_)都設定為Set xBook，這樣後面的執行都只要寫成xBook+........就好
Dim T As String, B As String, i As Integer, xBook As Workbook
T = "\\shl-group.com\dept\MFMG\B.各組資料 (Team inform)\E.生管組 (PPC group)\X.自動化工具(勿刪)\模具異動-六福(急件1.9專用)\"
For i = 0 To 10
B = Format(Date - i, "yyyy.mm.dd")
If Dir(T & B & "_每日模具異動.xlsx") <> "" Then
Set xBook = Workbooks.Open(T & B & "_每日模具異動.xlsx", ReadOnly:=True)
Exit For
ElseIf Dir(T & B & " 每日模具異動.xlsx") <> "" Then
Set xBook = Workbooks.Open(T & B & " 每日模具異動.xlsx", ReadOnly:=True)
Exit For
End If
Next
If xBook Is Nothing Then MsgBox "找不到目標檔案": Exit Sub
End If
xBook.Sheets("LF").Columns("A:AG").Cells.Copy
' ~ do something ~
xBook.Close 0
