wb.SaveAs() and wb.Close() close all open workbooks instead of just the dimensioned worbook objects of choice for VBA

139 Views Asked by At

I'm trying to run a script with VBA to loop through a bunch of excel workbooks and add a chart to each. The looping through workbooks, editing, saving, and closing is something I've done a million times. However, this time, everytime I run the wb.SaveAs, it closes all the open workbooks, including the macro enabled workbook that is running the script. I'm unsure why this is happening. No errors are being thrown because it is just closing the workbook. Any idea on how to fix this?

Sub addChartsAndFormatting()

Dim wb As Workbook
Dim wbMacro As Workbook
Dim ws As Worksheet
Dim i As Long
Dim j As Long
Dim k As Long
Dim lr As Long
Dim lc As Long
Dim fso As FileSystemObject
Dim ch As Chart
Dim dt As Range

'''''''''''''''''''''''''''''''''''''''''''''''''''''''
oFolderPath = "C:\Users\rs\3_EditedWithAnalyses"
saveFilePath = "C:\Users\rs\4_EditedWithCharts"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set fso = CreateObject("Scripting.FileSystemObject")
Set wbMacro = ThisWorkbook

For Each oFile In fso.GetFolder(oFolderPath).Files

    
    Set wb = Workbooks.Open(oFile.Path)
    Set ws = wb.Sheets("Sheet1")
    Set dt = Range("BG18:BJ18")
    Set ch = ws.Shapes.AddChart2(Style:=201, XlChartType:=xlColumnClustered, Left:=ws.Range("BE22")).Chart


    With ch
        .SetSourceData Source:=dt
        .ChartTitle.Text = "Seasonal Emissions Rate"
        .FullSeriesCollection(1).Name = "Sheet1!$BG$2:$BJ$2"
        .FullSeriesCollection(1).Values = "Sheet1!$BG$18:$BJ$18"
        .FullSeriesCollection(1).XValues = "Sheet1!$BG$2:$BJ$2"
        .HasLegend = False
        With .Axes(xlValue)
            .HasTitle = True
            With .AxisTitle
                .Caption = "Average Emissions Rate (lb CO2/MWh-gross)"
            End With
        End With
    End With
    
    
    rawFile = Split(oFile.Name, ".")
    saveFileName = rawFile(0) & "_complete.xlsx"
    wb.SaveAs (saveFileName) ' <------ ERROR IS HERE
    wb.Close
    
Next oFile



End Sub


0

There are 0 best solutions below