CODEWITHSUNDEEP
vbaexcel
Ciaran12
Ciaran12

Reputation: 51

How to open and activate another workbook in VBA?

I'm creating a macro where I will need to run it in 1 file (called "Masterfile"), it will open and execute the macro on another file ("SurveyReport") and then give me a message box saying "done!".

The code I have to execute on the SurveyReport file works fine when I open that file manually and execute it. The code I need to open SurveyReport from MasterFile is also working it seems, I ran the below with no issues:

    Sub PivotTable()
    '
    ' PivotTable Macro

    Dim MasterFile As String

    MasterFile = ActiveWorkbook.Name

    Dim SurveyReport As String

    SurveyReport = Application.GetOpenFilename("Excel files (*.xlsx), *xlsx", 1, "Please select the Survey Create Report file", , False)

    Workbooks.Open (SurveyReport)


    End Sub

But, when I try to activate the SurveyReport file so I can begin executing the macro in it, I get a "Subscript out of range" error. I've tried using the following code after the above block and before the code to execute in the SurveyReport file:

    Windows(SurveyReport).Activate

This didn't work, not did:

    ThisWorkbook.Activate

...which only had the effect of activating the MasterFile.

SurveyReport file is a .xlsx file. I tried saving it as a .xls file and amending the code, but no joy. I also tried passing it the file name directly (i.e. Windows("filename.xlsx").Activate), same issue.

Upvotes: 4

Views: 15585

Answers (2)

Eric Schyberg
Eric Schyberg

Reputation: 11

The part of the answer that is missing - is that he tried to call a method of an object when his variable was STRING - the open command and subsequent commands will give you an OBJECT - which has properties and methods like .Activate. The STRING type has no such method or property (to be sure - it may have others). the solution provided by Darren solves this by declaring his SurveyReport as type Workbook - an object of Excel.

Upvotes: 1

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

ActiveWorkbook is as it says on the tin - whichever workbook happens to be active when the code runs.
ThisWorkbook is always the workbook that the code is sitting in.

You can SET references to specific workbooks rather than just using their names each time. A name can change, or reference the wrong object.... imagine you have a friend called Darren. Each time you mention him you mention him by name. Someone that doesn't know Darren hasn't a clue which Darren out of all the ones available in the world you're talking about. Now imagine you have a little replica of Darren in your pocket... nah, that's a terrible anology - it wouldn't be a replica, it would be a reference to the real Darren... anyway, I digress.

This code sets a reference to the workbook, you can then use that reference any time you want to refer to the correct workbook:

Sub PivotTable()

    Dim MasterFile As Workbook

    Dim SurveyRptName As String
    Dim SurveyReport As Workbook

    Set MasterFile = ThisWorkbook '

    SurveyRptName = Application.GetOpenFilename("Excel files (*.xlsx), *xlsx", 1, _
        "Please select the Survey Create Report file", , False)
    If SurveyRptName <> "False" Then
        Set SurveyReport = Workbooks.Open(SurveyRptName)
    End If

    SurveyReport.Activate 'You don't need this line.  It doesn't matter if
                          'the workbook is active, the code knows which one
                          'you're talking about in the next line.
    MsgBox "This is " & SurveyReport.Name & _
        " containing " & SurveyReport.Worksheets.Count & " sheets." & vbCr & _
        "The value in cell A1 of the first sheet is " & _
            SurveyReport.Worksheets(1).Range("A1")

End Sub

Edit: Of course, if you press Cancel when selecting a file then the lines following the IF...THEN code won't have a reference to work on and you'll get a Object Variable or With block variable not set - best not to run the bottom bit of code if you haven't successfully opened the Survey Report file.

Upvotes: 3

Related Questions