VBA add on query...

[James Lomas]

Good afternoon - new here so please be gentle as i'm a total novice at VBA coding...

I have a master spreadsheet that draws information from 3 password protected workbooks. In order to open then I have got the following code:

Sub OpenSesame()
Workbooks.Open Filename:="C:\folder\sub folder\file.xlsm", Password:="password"
Workbooks.Open Filename:="C:\folder\sub folder\file2.xlsm", Password:="password"
Workbooks.Open Filename:="C:\folder\sub folder\sub folder 2\file12.xlsm", Password:="password"

End Sub

Which is great - however it opens all three files - is there an additional code that will close the newly opening spreadsheets?!

Thanks
James

 

[mumps]

Place the macro in a standard module in your master workbook and run it from there. The macro assumes that only the Master and the other 3 workbooks are open.

Sub OpenSesame()
    Dim wb As Workbook, desWB As Workbook
    Set desWB = ThisWorkbook
    Workbooks.Open Filename:="C:\folder\sub folder\file.xlsm", Password:="password"
    Workbooks.Open Filename:="C:\folder\sub folder\file2.xlsm", Password:="password"
    Workbooks.Open Filename:="C:\folder\sub folder\sub folder 2\file12.xlsm", Password:="password"
    For Each wb In Workbooks
        If wb.Name <> desWB.Name Then
            wb.Close False
        End If
    Next wb
End Sub

 

[Joe4]

If you want/need to control each workbook individually, one way is to assign them to specific worksheet variables.
That just makes it a bit easier to bounce around from one to the other.

Sub OpenSesame()
    Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    Workbooks.Open Filename:="C:\folder\sub folder\file.xlsm", Password:="password"
    Set wb1 = ActiveWorkbook
    Workbooks.Open Filename:="C:\folder\sub folder\file2.xlsm", Password:="password"
    Set wb2 = ActiveWorkbook
    Workbooks.Open Filename:="C:\folder\sub folder\sub folder 2\file12.xlsm", Password:="password"
    Set wb3 = ActiveWorkbook
End Sub

Then you can activate a specific workbook like this:

wb1.Activate

or close it like this:

wb1.Close