Formatting Excel file after Export 1

[ba4crm]

I am using the following code to format an Excel file that I am creating using:
DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, XFile, False

Excel formatting:
******************************
Public Sub ModifyExportedExcelFileFormats(XFile As String)
On Error GoTo Err_ModifyExportedExcelFileFormats

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(XFile).Sheets(1)

With xlApp
.Application.Sheets("qrpt_DR").Select
.Application.Cells.Select
.Application.Cells.Font.Name = "Century Gothic"
.Application.Cells.Font.Size = 10
.Application.Selection.Columns.AutoFit
.Application.Selection.Rows.AutoFit
.Application.ActiveWorkbook.Save
.Application.ActiveWorkbook.Close

.Quit
End With

Set xlApp = Nothing
Set xlSheet = Nothing

Exit_ModifyExportedExcelFileFormats:
Exit Sub

Err_ModifyExportedExcelFileFormats:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_ModifyExportedExcelFileFormats

End Sub
***************************

However, I get the error message:
-214741851 - Method 'Save' of Object '_Workbook' failed. I have not been able to find any information on the error message and how to get around it.

 

[vbajock]

Are you sure no one else is using the file? I can't duplicate the error using Excel/Access 2003, so that is a likely possibility, either you or another user has a copy open.

Another possibility is you need to update your service pack level, since what I did find googling your error was a reported bug in the SaveAs method, which might also affect Save.

Alternative code you might try:

.Application.ActiveWorkbook.Close savechanges:=True

Also, in your error handler, you need to add the code


Err_ModifyExportedExcelFileFormats:
MsgBox Err.Number & " - " & Err.Description
->> xlApp.Application.ActiveWorkbook.Close SaveChanges:=False



if you do not, your active object will stay in memory if you exit on error.

 

[ba4crm]

I think it is a bug in my Excel. I have had issues recently trying to import an Excel to Access. Tried the same code on another users machine - worked like a gem!
Thanks for the tip on the error handler.