If you don't activate each sheet then selecting A1 will not work (except on
the active sheet), so you might as well get rid of that line.

Prefix every range/cell reference with wks, or use With:

With wks
.Range("A1").Font.Bold= True
'etc
End With
or

wks.Range("A1").Font.Bold= True

You have a "With wks" but it looks like your formatting comes after it.
Move it inside it and of course use "." .


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