Xloopup 個date
Default會由上至下Return一個Result比你
@PYTHON數據大師 VBA 幫一堆EXCEL檔加VBA SCRIPT, VBA SCRIPT而存成TXT檔, 怎樣做
Sub AddVBAScriptToWorkbooks()
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim textToImport As String
Dim vbaScript As String
Dim textFile As Integer
' 確認腳本檔案的路徑
vbaScriptPath = "C:\YourFolder\YourScript.txt"
' 指定文件夾路徑
myPath = "C:\YourFolder\"
' 指定文件擴展名
myExtension = "*.xls*"
' 為了安全, 關閉屏幕更新
Application.ScreenUpdating = False
' 打開文本檔案含有的VBA腳本
textFile = FreeFile
' 這裡需要用附加的方式打開文本檔案來讀取
Open vbaScriptPath For Append As textFile
vbaScript = Input$(LOF(textFile), textFile)
Close textFile
' 搜索目標文件夾內的所有Excel檔案
myFile = Dir(myPath & myExtension)
' 使用循環遍歷所有文件
Do While myFile <> ""
' 打開其中一個工作簿
Set wb = Workbooks.Open(Filename:=myPath & myFile)
' 這裡添加腳本到工作簿
With wb.VBProject.VBComponents.Import(vbaScriptPath)
' 假設VBA腳本就在文本檔案中
.CodeModule.AddFromString vbaScript
End With
' 保存並關閉當前工作簿
wb.Close SaveChanges:=True
' 移至下一個檔案
myFile = Dir
Loop
' 開啟屏幕更新
Application.ScreenUpdating = True
MsgBox "所有的工作簿都已被添加VBA腳本並儲存。"
End Sub
Sub doing_your_job()
Dim AK As Workbook, OAK As Workbook
Dim LastR As Variant
Set fso = CreateObject("Scripting.FileSystemObject")
Set OAK = ActiveWorkbook
Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.Show
MsgBox diaFolder.SelectedItems(1)
Application.ScreenUpdating = False
Set FD = fso.GetFolder(diaFolder.SelectedItems(1))
For Each F In FD.Files
If F.Type = "Microsoft Excel Worksheet" Then '哩度睇你OS點叫佢, 右鍵->內容, 如果食XLSX+XLS就用OR
Set AK = Workbooks.Open(F.Path, , True)
AK.Sheets(1).Activate
doing_your_job..................
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub