Since you want to select A1 on each sheet you have to activate each one.
Then your other code will work too.

With objActiveWkb
For Each wks In .worksheets
wks.Activate
MaxRows = wks.Rows.Count
MaxColumns = wks.Columns.Count
' (Series of formatting steps in Excel
Cells.EntireColumn.AutoFit
Range("A1").Select
Next wks
End With

--
Jim
"EagleOne@microsoftdiscussiongroups"
rosoft.com wrote in
message ...
| 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
|