我有一个 Book1.xls Excel 工作簿,其中编写了一个宏,以便在工作簿上打开宏运行。此宏采用工作簿路径中的所有 CSV 文件,并将所有 CSV 合并到一个工作表中,例如 Master.xlsx这工作正常并创建 Master.xlsx。在此宏结束时,我调用在同一工作表的模块中编写的另一个宏,并将 Master.xlsx 引用作为工作簿参数传递给另一个宏
现在我想要的是我需要设置 Master.xlsx将参数传递给这个宏(模块(作为当前/活动工作簿,以便我可以格式化 master 的内容.xlsx
我的 Book1.xls 代码是:
Private Sub Workbook_Open()
'Create Excel application instance
Dim xlApp As Object
Dim dt, masterpath, folderPath, fileName, dtFolder As String
Set xlApp = CreateObject("Excel.Application")
'Setup workbooks
Dim wb As Excel.Workbook
Dim wBM As Excel.Workbook
Dim Wk As Workbook
fileName = "C:Master.xlsx"
'Create a new Workbook
Set Wk = Workbooks.Add
Application.DisplayAlerts = False
Wk.SaveAs fileName:=fileName
Wk.Close SaveChanges:=False
Application.DisplayAlerts = True
'Csv files folder
Dim CSVfolder As String
CSVfolder = masterpath
'Master Excel file path
Dim mF As String
mF = fileName 'Where your master file is
'open the master file
Set wBM = xlApp.Workbooks.Open(mF)
'search and open the client files
Dim fname As String
fname = Dir(CSVfolder & "*.csv")
Do While fname <> ""
'open the client file
Set wb = xlApp.Workbooks.Open(CSVfolder & "" & fname)
'copy the first sheet from client file to master file
wb.Sheets(1).Copy After:=wBM.Sheets(wBM.Sheets.count)
'save master file
wBM.Save
'close client file
wb.Close False
'move to next client file
fname = Dir()
Loop
xlApp.Visible = True
Set xlApp = Nothing
Call AnotherMacroInModuleOfSameWorkbook(wBM)
End Sub
同一工作簿模块中的宏代码
Sub AnotherMacroInModuleOfSameWorkbook(wb As Workbook)
wb.Activate
MsgBox (wb.Name)
MsgBox (ActiveWorkbook.Name)
End Sub
在这里,我得到警报 1 的">Master.xlsx"和警报 2 的">Book1.xls">
我想要的是,由于我从上面的宏传递 Master.xlsx 的引用,然后在下面的宏中激活 Master.xlsx,警报 2 应该给出"Master.xlsx"作为警报。
请帮忙。
谢谢。
通过更改此行,主工作表现在打开,这是以前没有打开的。 它只是访问它。 我使用自己的工作簿进行了测试,并使用了您的代码作为基础。 但是,我没有使用您的所有代码,因为我没有这些对象。 所以它主要是经过测试的。 在使用这一行解决之前,我确实生成了您遇到的相同错误,所以我非常确定这可以解决您的问题:
Set wBM = Application.Workbooks.Open(mF)
问题是,当您打开它时,代码会中断并需要继续。 要解决此问题,您需要在打开工作簿之前放置以下行。
Application.EnableCancelKey = xlDisabled
请注意:如果这样做,则在生成无限循环时将无法破坏代码。
请参阅这篇文章,了解如何处理启用取消密钥
您还尝试打开一个.xlsx文件,而不是 .xlsm 在文件创建语句中包含此内容。
FileFormat:= _xlOpenXMLWorkbookMacroEnabled
我找到了解决此问题的方法。我尝试关闭生成的主文件(wBM(,然后再次使用工作簿(mF(打开主工作簿。打开,最终给了我当前的工作簿(主(作为活动工作簿。凤凰网..!!!艰难时期
以下是当前工作代码的快照:
Private Sub Workbook_Open()
'Create Excel application instance
Dim xlApp As Object
Dim dt, masterpath, folderPath, fileName, dtFolder As String
Set xlApp = CreateObject("Excel.Application")
'Setup workbooks
Dim wb As Excel.Workbook
Dim wBM As Excel.Workbook
Dim Wk As Workbook
fileName = "C:Master.xlsx"
'Create a new Workbook
Set Wk = Workbooks.Add
Application.DisplayAlerts = False
Wk.SaveAs fileName:=fileName
Wk.Close SaveChanges:=False
Application.DisplayAlerts = True
'Csv files folder
Dim CSVfolder As String
CSVfolder = masterpath
'Master Excel file path
Dim mF As String
mF = fileName 'Where your master file is
'open the master file
Set wBM = xlApp.Workbooks.Open(mF)
'search and open the client files
Dim fname As String
fname = Dir(CSVfolder & "*.csv")
Do While fname <> ""
'open the client file
Set wb = xlApp.Workbooks.Open(CSVfolder & "" & fname)
'copy the first sheet from client file to master file
wb.Sheets(1).Copy After:=wBM.Sheets(wBM.Sheets.count)
'save master file
wBM.Save
'close client file
wb.Close False
'move to next client file
fname = Dir()
Loop
'close the current workbook
wBM.Close False
xlApp.Visible = True
Set xlApp = Nothing
'setting the reference again
Set newfile = Workbooks.Open(mF)
MsgBox (newfile.Name)
MsgBox (ActiveWorkbook.Name)
'Call to another module
Call AnotherMacroInModuleOfSameWorkbook(wBM)
End Sub
这两行起到了作用:
'close the current workbook
wBM.Close False
'setting the reference again
Set newfile = Workbooks.Open(mF)
感谢您的所有答案。