Excel workbook changes won't save from Access macro

[lowells]

Hey,
I've written the code below to export then format a report from Access 2013 to an Excel workbook. It works great until it gets to the xlWb.Save line where I get the following error.

Run-time error '1004':
Method 'Save' of object '_Workbook' failed

I suspect the problem might have something to do with me running Office 2013 but trying to save as a .xls file which is an older compatibility mode version. But I'm just guessing.


Any thoughts on how I can get these changes to save?

Option Compare Database

Sub ExportOpenItemsToExcel()
' first export the raw report data to an Excel workbook in the currentdb's folder
    sPath = CurrentProject.Path
    DoCmd.OutputTo acOutputReport, "rptOpenActionItems", acFormatXLS, sPath & "\OpenActionItemsReport.xls", False
    
    
    'now format the Excel Workbook from this Access macro
    Dim xlAPP As Object
    Dim xlWS As Object
    Dim xlWb As Excel.Workbook
    
    Set xlAPP = CreateObject("excel.application")
    Set xlWb = xlAPP.Workbooks.Open(sPath & "\OpenActionItemsReport.xls")
    Set xlWS = xlWb.Worksheets("Open Action Items")
    xlAPP.Visible = True

    xlWS.Range("A1:H2").Font.Bold = True
    xlWS.Range("A1:H2").HorizontalAlignment = xlCenter
    xlWS.Cells(1, 2).NumberFormat = "[$-F400]h:mm:ss AM/PM"
    xlWS.Columns("C:C").ColumnWidth = 14.14
    
    xlWb.Save
    
    Set xlWS = Nothing
    Set xlWb = Nothing
    Set xlAPP = Nothing

End Sub

[aytee111]

Try "xlWB.SaveAs sPath & "\OpenActionItemsReport.xls",FileFormat:=1

No guarantees!

[lowells]

Thanks! That worked!

[aytee111]

How nice!

Please mark it solved.