2003

Assume Access just completed VBA transfering 21 Tables to 21 XL spreadsheets
on One workbook.

Then, via Automation, I wish to format all 21 sheets in the XLS w/b file.

The Automation For Next loop does not cycle through all W/S's in the XL w/b
file. It just formats the first w/s 21 times.

I then thought that maybe I needed to save the XL w/b with 21 sheets first,
then attempt to format all of them.

At EOD, I want to have all 21 sheets in one w/b looped through the
FormatXLSheets Sub.

My Automation code (99% Dev Ashish's Automation Code) follows:

Would someone review my code to ascertain why it does not sequence through
to 21 XL w/s?

Dave Hargis, Microsoft Access MVP AKA Klatuu, feels that the late binding
and after Dimming: Dim wks As Object, is causing VBA

Then the following loop does not work (does not cycle through all sheets -
loops through sheet1 21 times) because VBA is not sure what to do with
"wks" as Dimmed As Object vs Dimmed as Worksheet.

For Each wks in Worksheets

Next wks

Any work-arounds; additional thoughts?

TIA EagleOne
************************************************** **

Sub FormatXLSheets(myPathFile As String, myFileName As String)
'
'Code Courtesy of Dev Ashish
'
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

' Variables related to Excel Formatting
Dim wks As Object
Dim myRange As Object
Dim myRange2 As Object
Dim myCell As Object
Dim myRowsToProcess As Long
Dim myColumnsToProcess As Long
Dim MaxRows As Long
Dim MaxColumns As Long

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

With objXL.Application
..Visible = False
..workbooks.Open myPathFile
End With
Set objActiveWkb = objXL.Application.ActiveWorkBook

With objActiveWkb
'
Set wks = Nothing
For Each wks In .worksheets
With wks
MaxRows = .Rows.Count
MaxColumns = .Columns.Count
End With
'
'
'
' (Series of formatting steps in Excel
'
'

Cells.EntireColumn.AutoFit
Range("A1").Select
Next wks
End With

objActiveWkb.Close savechanges:=True

'If boolXL Then objXL.Application.Quit

objXL.Application.Quit

Set objActiveWkb = Nothing: Set objXL = Nothing

End Sub