我正在嘗試打開此范圍內的所有作業簿并運行它們的宏。這個想法是打開第一個作業簿，運行宏，然后在繼續下一個之前關閉它。宏的名稱在所有作業簿中都相同。我正在運行代碼，但出現（下標超出范圍）錯誤。任何幫助將非常感激。
Sub files_export()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim startsheet As Worksheet
Set startsheet = wb.Worksheets("Start")
Dim linkrange As Range
Set linkrange = startsheet.Range("C4:C21")
Dim y As String
Dim c As Range
For Each c In linkrange
If c.Value <> "" Then
y = c.Value
Workbooks.Open (y)
Workbooks(y).Application.Run ("macro1")
Workbooks.close (y)
End If
Next c
End Sub
uj5u.com熱心網友回復：
在另一個作業簿中運行宏
Option Explicit
Sub FilesExport()
Const dName As String = "Start"
Const FilePathsRangeAddress As String = "C4:C21"
Const ModuleName As String = "" ' or e.g. "Module1"
Const MacroName As String = "Macro1"
Dim ModName As String: ModName = ModuleName
If Len(ModName) > 0 Then ModName = ModName & "."
Dim dwb As Workbook: Set dwb = ThisWorkbook
Dim dws As Worksheet: Set dws = dwb.Worksheets(dName)
Dim fprg As Range: Set fprg = dws.Range(FilePathsRangeAddress)
Dim swb As Workbook
Dim swbPath As String
Dim sCell As Range
Dim sMacroString As String
For Each sCell In fprg.Cells
swbPath = CStr(sCell.Value)
If Len(swbPath) > 0 Then
If Len(Dir(swbPath)) > 0 Then
Set swb = Workbooks.Open(swbPath)
sMacroString = "'" & swb.Name & "'!" & ModName & MacroName
swb.Application.Run sMacroString
swb.Close SaveChanges:=False ' maybe 'True' ?
End If
End If
Next sCell
End Sub
uj5u.com熱心網友回復：
請嘗試更換：
Workbooks(y).Application.Run ("macro1")
和：
Application.Run y & "!.macro1"
注1：macro1應該存在于標準模塊中。
注意2：使用作業簿全名將使y作業簿打開，如果尚未打開...您可以使用作業簿Name，從全名中提取它，但這僅在討論中的作業簿打開時才有效（就像您的情況一樣）。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/401409.html