Jim,

I figured that the answer would be fixing a simple step.

That said, what else can I do besides wks.activate?

If you notice, in the Late-binding code I:
used "With objXL.Application.Visible = False"

Adding wks.activate to the loop caused all instances of Excel, in turn,
initiated by 21 DoCmd.TransferSpreadSheet's to stay active. Also,
none of the sheets were formatted.

It seems that I traded my original challenge of having multiple-sheet
workbooks have all sheets formated (in lieu of just the 1st sheet) and all
instances of Excel closed to the inverse of both respectively.

Now, I further believe that I am missing a different simple step to make all
work.

Thoughts?

TIA EagleOne

"Jim Rech" wrote:

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
|