0

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


  • I implemented a simplified version of your code on my machine and cannot reproduce the error. – Manny Aug 15 '22 at 15:09
  • Interesting. I've used the wb.Close and wb.SaveAs commands many times before and not had issues. Unsure if this script is "corrupted" or something like that. I did try creating a new macro file and copied in the script and it still didn't work. I tried to copy the data I was reading into a new file and that didn't work either. – Richard Strott Aug 15 '22 at 16:01
  • Did you verify the file type of the files you are opening? Eg not trying to save an xlsm as xlsx etc – Manny Aug 15 '22 at 17:13
  • Yes, it was verified. I've also had other people try it with success. I'm assuming that there is file corruption somewhere that I have not been able to address. – Richard Strott Aug 16 '22 at 14:01

0 Answers0