Workbook not activating in Excel by passing as an argument

Question

I have a Book1.xls Excel workbook which has a macro written so that on workbook open the macro runs. This macro takes all the CSV files in the workbook path and merges all the CSV into a single sheet say Master.xlsx which works fine and creates the Master.xlsx. At the end of this macro I am calling another macro written in a module of same sheet and passing the Master.xlsx reference as workbook argument to another macro

Now what I want is that I need to set Master.xlsx passed an argument to this macro(module) as the current/active workbook so that I can format contents of the master.xlsx

My Code for the Book1.xls is :

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 Macro in Module of same Workbook

Sub AnotherMacroInModuleOfSameWorkbook(wb As Workbook)

wb.Activate
MsgBox (wb.Name)
MsgBox (ActiveWorkbook.Name)

End Sub

Here I'm getting "Master.xlsx" for alert 1 and "Book1.xls" for alert 2

What I wanted was that since I am passing reference of the Master.xlsx from the above macro and then activating the Master.xlsx in the below macro, the alert 2 should have given "Master.xlsx" as alert.

Please help.

Thanks.

A detail: you probably want Dim dt As String, masterpath As String, ... As String instead of Dim dt, masterpath, ... As String. — sancho.s ReinstateMonicaCellio
@sancho.s : As I said first macro is running properly and generating the Master.xlsx. So above syntactical error which you pointed out is not valid. All works fine. You seem to have misread my issue, I am facing a problem with the reference of the new sheet which is generated by the first macro in the second macro. i.e I am not able to reference the Master.xlsx passed as a Workbook object from the calling macro into the called macro. Thanks. — Suvojit
What you find is strange to me also. Something to try: place a breakpoint at wb.Activate, and have your usual macro sequence run. After execution breaks, execute ? wb.Name, ActiveWorkbook.Name in the immediate window prior to stepping over each of the next 3 lines. You might find your answer there. — sancho.s ReinstateMonicaCellio
Instead of variable 'wb' in 'wb As Workbook' use something else and try like you can use 'mwb as WorkBook'. — Paresh J
@PareshJ : Tried but didn't work.Problem here is the workbook is not getting activated. MsgBox (wb.Name) wb.Activate MsgBox (ActiveWorkbook.Name) .First alert says "Master.xlsx" and second says "Book1.xlsx" even after actiavting Master.xlsx — Suvojit

peege peege · Accepted Answer · 2014-12-18T10:53:04

By changing this line, the Master sheet opens now, where it wasn't before. It was just accessing it. I tested using my own workbooks, and used your code as a base. However, I didn't use all of your code, being that I don't have those objects. So it's mostly tested. I did generate the same errors you got before solving with this line, so I'm very certain this solves your problem:

Set wBM = Application.Workbooks.Open(mF)

The problem there is that when you open it, the code will break and need to be continued. To solve that, you need to place the following line before opening the workbook.

Application.EnableCancelKey = xlDisabled

BE WARNED: If you do this, you will not be able to break your code if you generate an infinite loop.

Please see this article about how to deal with EnableCancelKey

You are also trying to open a .xlsx file, instead of .xlsm Include this with your file creation statements.

FileFormat:= _xlOpenXMLWorkbookMacroEnabled

Workbook not activating in Excel by passing as an argument

2 Answers