Closing Other Workbooks using VBA

[Grothgar]

Hi All;

I was having problems with linking Pivot Data through VBA because sometimes it would not be able to open the file correctly to get the data.
To fix this, I just had the Macro open the Workbooks first, and then link to them, which has solved that problem.

However, having opened these specific workbooks, I can't find a way to close them! Now, as problems go, this is not USA vs Russia Cold War problems, but it offends my sense of neatness!

So, this is the start of the Macro (where I open the Workbooks)

MONTHP = Sheets("Front Sheet").Range("B12").Value
CQUARTER = Sheets("Front Sheet").Range("B12").Value
NQUARTER = Sheets("Front Sheet").Range("B12").Value
FULLYEAR = Sheets("Front Sheet").Range("B12").Value
MONTHP2 = Sheets("Front Sheet").Range("B15").Value
CQUARTER2 = Sheets("Front Sheet").Range("B16").Value
NQUARTER2 = Sheets("Front Sheet").Range("B17").Value
FULLYEAR2 = Sheets("Front Sheet").Range("B18").Value
'
Application.ScreenUpdating = False
Workbooks.Open (MONTHP & MONTHP2)
Workbooks.Open (CQUARTER & CQUARTER2)
Workbooks.Open (NQUARTER & NQUARTER2)
Workbooks.Open (FULLYEAR & FULLYEAR2)
ThisWorkbook.Activate

And this is the end of the Macro, where I am attempting to close those very same Workbooks

Workbooks(MONTHP & MONTHP2).Select
Workbooks(MONTHP & MONTHP2).Close False
Workbooks(CQUARTER & CQUARTER2).Select
Workbooks(CQUARTER & CQUARTER2).Close False
Workbooks(NQUARTER & NQUARTER2).Select
Workbooks(NQUARTER & NQUARTER2).Close False
Workbooks(FULLYEAR & FULLYEAR2).Select
Workbooks(FULLYEAR & FULLYEAR2).Close False

So, what am I doing wrong?

 

[VoG]

Maybe like this

Dim wb As Workbook
For Each wb In Workbooks
    If wb.Name <> ThisWorkbook.Name Then wb.Close False
Next wb

 

[Richard Schollar]

So what happens when you run the code? Do you get errors? If so, on which line? What is the specific error (number plus description, please)?

Workbooks collection doesn't have a Select method, so those lines would error. You don't need to activate the workbook before you close it so you can just use:

Workbooks(MONTHP & MONTHP2).Close False

Workbooks(CQUARTER & CQUARTER2).Close False

Workbooks(NQUARTER & NQUARTER2).Close False

Workbooks(FULLYEAR & FULLYEAR2).Close False

 

[Grothgar]

Maybe like this

Dim wb As Workbook
For Each wb In Workbooks
    If wb.Name <> ThisWorkbook.Name Then wb.Close False
Next wb

That is what I originally had, but that would close down any other workbooks that they had open at the same time, without saving... I can hear the Howl's of Dismay already!

Thanks though.

 

[Grothgar]

So what happens when you run the code? Do you get errors? If so, on which line? What is the specific error (number plus description, please)?

Workbooks collection doesn't have a Select method, so those lines would error. You don't need to activate the workbook before you close it so you can just use:

Workbooks(MONTHP & MONTHP2).Close False
 
Workbooks(CQUARTER & CQUARTER2).Close False
 
Workbooks(NQUARTER & NQUARTER2).Close False
 
Workbooks(FULLYEAR & FULLYEAR2).Close False

Hmmm, when I use this, I get the following error

Run-time error '9':
Subscript out of range

And it is showing the start of this part of the Macro as the error.

Workbooks(MONTHP & MONTHP2).Close False
Workbooks(CQUARTER & CQUARTER2).Close False
Workbooks(NQUARTER & NQUARTER2).Close False
Workbooks(FULLYEAR & FULLYEAR2).Close False

I can't see a reason why this would not work... combining two named cells works for opening the file, why not closing it?

 

[Richard Schollar]

Please provide an example value of MONTHP & MONTHP2 that isn't working - do you change the values of either variable over the course of the macro? Or change the workbook itself (eg by using SaveAs)?

 

[Grothgar]

Please provide an example value of MONTHP & MONTHP2 that isn't working - do you change the values of either variable over the course of the macro? Or change the workbook itself (eg by using SaveAs)?

MONTHP = Sheets("Front Sheet").Range("B12").Value
MONTHP2 = Sheets("Front Sheet").Range("B15").Value

MONTHP = \\pdataserv\userdata\D and A\COMMERCIAL REVIEW\FINANCE\FY12\F04\PIVOT DATA\
MONTHP2 = ACT_JUL_PIV_DATA_v9.xls

I do not change the values of each variable over the course of the Macro... the Macro runs like this so that each month the links can be updated, without having to go into VBA each time. I will not be the one using this sheet, so I have to make it as userfriendly as possible.

Here is the entire Macro.

Sub PivotUpdate()
'
' PivotUpdate Macro
'
MONTHP = Sheets("Front Sheet").Range("B12").Value
CQUARTER = Sheets("Front Sheet").Range("B12").Value
NQUARTER = Sheets("Front Sheet").Range("B12").Value
FULLYEAR = Sheets("Front Sheet").Range("B12").Value
MONTHP2 = Sheets("Front Sheet").Range("B15").Value
CQUARTER2 = Sheets("Front Sheet").Range("B16").Value
NQUARTER2 = Sheets("Front Sheet").Range("B17").Value
FULLYEAR2 = Sheets("Front Sheet").Range("B18").Value
'
Application.ScreenUpdating = False
Workbooks.Open (MONTHP & MONTHP2)
Workbooks.Open (CQUARTER & CQUARTER2)
Workbooks.Open (NQUARTER & NQUARTER2)
Workbooks.Open (FULLYEAR & FULLYEAR2)
    ThisWorkbook.Activate
    Sheets("Pivot Data").Visible = True
    Sheets("Pivot Data").Select
    Range("A2").Select
    ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        MONTHP & "[" & MONTHP2 & "]Sheet1!R1C1:R10000C83", Version:=xlPivotTableVersion12)
    Range("AD2").Select
    ActiveSheet.PivotTables("PivotTable10").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        CQUARTER & "[" & CQUARTER2 & "]Sheet1!R1C1:R10000C83", Version:=xlPivotTableVersion12)
    Range("BG2").Select
    ActiveSheet.PivotTables("PivotTable11").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        NQUARTER & "[" & NQUARTER2 & "]Sheet1!R1C1:R10000C83", Version:=xlPivotTableVersion12)
    Range("CJ2").Select
    ActiveSheet.PivotTables("PivotTable12").ChangePivotCache ActiveWorkbook. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        FULLYEAR & "[" & FULLYEAR2 & "]Sheet1!R1C1:R12000C83", Version:=xlPivotTableVersion12)
    Range("A1").Select
    Sheets("Month").Select
    Range("F17").Select
    ActiveSheet.PivotTables("PivotTable13").ChangePivotCache ( _
        "Pivot Data!PivotTable2")
    Sheets("Current Quarter").Select
    Range("F17").Select
    ActiveSheet.PivotTables("PivotTable13").ChangePivotCache ( _
        "Pivot Data!PivotTable10")
    Sheets("Next Quarter").Select
    Range("F17").Select
    ActiveSheet.PivotTables("PivotTable13").ChangePivotCache ( _
        "Pivot Data!PivotTable11")
    Sheets("Full Year").Select
    Range("F17").Select
    ActiveSheet.PivotTables("PivotTable13").ChangePivotCache ( _
        "Pivot Data!PivotTable12")
    Sheets("Variances").Select
    ActiveSheet.PivotTables("PivotTable14").PivotSelect "Category", xlButton, True
    Range("A3").Select
    ActiveSheet.PivotTables("PivotTable14").ChangePivotCache ( _
        "Pivot Data!PivotTable2")
    Range("A43").Select
    ActiveSheet.PivotTables("PivotTable15").ChangePivotCache ( _
        "Pivot Data!PivotTable10")
    Range("A83").Select
    ActiveSheet.PivotTables("PivotTable16").ChangePivotCache ( _
        "Pivot Data!PivotTable11")
    Range("A123").Select
    ActiveSheet.PivotTables("PivotTable17").ChangePivotCache ( _
        "Pivot Data!PivotTable12")
    Range("A1").Select
    Sheets("Pivot Data").Visible = False
    Sheets("Front Sheet").Select
Workbooks(MONTHP & MONTHP2).Close False
Workbooks(CQUARTER & CQUARTER2).Close False
Workbooks(NQUARTER & NQUARTER2).Close False
Workbooks(FULLYEAR & FULLYEAR2).Close False
Application.ScreenUpdating = True
End Sub

EDIT - SORTED! I realised that I did not need to include the MONTHP/CQUARTERP etc in the line, because they were already open, so I only needed the Excel Part, rather than the File Location as well!

Thanks for your help!