Workbook is not activated in Excel by passing as an argument
I have an Excel workbook Book1.xls Excel, in which there is a macro written in such a way that the workbook is executed macro. This macro takes all CSV files in the book path and concatenates all CSVs into one sheet: Master.xlsx which works fine and creates Master.xlsx. At the end of this macro, I call another macro, written in a module of the same sheet, and passing the Master.xlsx reference as a workbook argument to another macro
Now what I want is that I need to set that Master.xlsx passed an argument to this macro (module) as the current / active workbook so that I can format the contents of master.xlsx
My code for 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
Code for a macro in a module of the same book
Sub AnotherMacroInModuleOfSameWorkbook(wb As Workbook)
wb.Activate
MsgBox (wb.Name)
MsgBox (ActiveWorkbook.Name)
End Sub
Here I am getting Master.xlsx "for alert 1 and Book1.xls " for alert 2
What I wanted was that since I am passing the Master.xlsx reference from the above macro and then activating Master.xlsx in the next macro, warning 2 should have given "Master.xlsx" as a warning.
Please, help.
Thank.
source to share
By changing this line, the master list opens now, where it was not before. He just got access to it. I tested my own books and used your code as a base. However, I haven't used all of your code as I don't have these objects. Therefore, it was mostly tested. I did generate the same errors you got before solving this line, so I am very confident that this solves your problem:
Set wBM = Application.Workbooks.Open(mF)
The problem is that when you open it, the code will break and must be continued. To solve this problem, you need to put the following line before opening the workbook.
Application.EnableCancelKey = xlDisabled
WARNING: . If you do this, you cannot break your code if you create an infinite loop.
See this article on how to work with EnableCancelKey
You are also trying to open the .xlsx file instead of .xlsm Include this with your file creation statements.
FileFormat:= _xlOpenXMLWorkbookMacroEnabled
source to share
I found a workaround for this problem. I tried to close the created master file (wBM) and reopen the master workbook with Workbooks (mF). Open, which ultimately gave me the current book (Master) as the active book. Phewww .. !!!! Tough time
Here's a snapshot of the current working code:
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
These two lines did the trick:
'close the current workbook
wBM.Close False
'setting the reference again
Set newfile = Workbooks.Open(mF)
Thanks for all the answers.
source to share