I'm trying to format an Excel worksheet from within Access XP, and I'm
having a few leetle problems. The main one is that the Save method is
not saving the updated workbook.

The following code manages to open the workbook, and I can see the
format changes happening on the screen, but the updated workbook is not
saved:

Public Sub FormatWorksheet(strExcelFilePath As String)
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim objXLRangeHeader As Excel.Range

Set objXLBook = GetObject(strExcelFilePath)
Set objXLSheet = objXLBook.Worksheets(1)
Set objXLRangeHeader = objXLSheet.Range("A1:M1")
Set objXLApp = objXLBook.Parent
objXLApp.Visible = True
objXLSheet.Visible = True

objXLRangeHeader.Font.Bold = True
objXLRangeHeader.WrapText = True
objXLSheet.Columns("A:M").AutoFit
objXLBook.Save
objXLBook.Close
End Sub

Any help would be appreciated.

Hi,

I'm trying to format an Excel worksheet from within Access XP, and I'm
having a few leetle problems. The main one is that the Save method is
not saving the updated workbook.

I would change the code to this:

Public Sub FormatWorksheet(strExcelFilePath As String)
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet
Dim objXLRangeHeader As Excel.Range

Set objXLApp = new Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(strExcelFilePath)
Set objXLSheet = objXLBook.Worksheets(1)
Set objXLRangeHeader = objXLSheet.Range("A1:M1")
Set objXLApp = objXLBook.Parent
objXLApp.Visible = True
objXLSheet.Visible = True

objXLRangeHeader.Font.Bold = True
objXLRangeHeader.WrapText = True
objXLSheet.Columns("A:M").AutoFit
objXLBook.Save
objXLBook.Close
Set objXLRangeHeader = Nothing
Set objXLSheet = Nothing
Set objXLBook = Nothing
objXLApp.Quit

Set objXLApp = Nothing
End Sub

Any help would be appreciated.



Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

This code is working after all - I was looking at the wrong copy of the
workbook (covers head with coat).