Formatting Excel Worksheets from Access???

[ejc00]

Hello,

I'm exporting multiple tables from Access to Excel using the TransferSpreadsheet function. I loop through the function such that the table name is a variable. The code then hits a function which formats the worksheet which i exported. I use the following code to activate the worksheet:

XL.Sheets(strTempFileName).Activate

The code runs through this fine... However, when I get to the code which formats the Sheet, which looks like this:

Cells.Select
Selection.ColumnWidth = 73.29
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit
Selection.Borders(xlDiagonalDown).LineStyle = xlNone

I get the following error:
Run-time error 1004: Method 'Cells' of object '_Global' failed.

What do I need to do to avoid this?

Thanks

 

[gbowen]

Try including the worksheet reference like this....

ws.Cells.Select
ws.Cells.EntireColumn.AutoFit