[AccessD] Access/Excel formatting problem

Does it hapopen on the second call to the sub  :-)

I uspect the old "unqualified referrence" problem

http://support.microsoft.com/kb/319832

<quote> When you write code to use an Excel object, method, or property,
you should always precede the call with the appropriate object variable.
</quote>

(That's my fifth posting of this quote to this list in 2 1/2 years <g>)

-- 
Stuart

On 23 Jan 2011 at 11:46, Doug Steele wrote:

> Hello All:
> 
> I am creating an Excel 2003 workbook from Access.  I need to apply
> standard formatting to each sheet, so I thought I would build a
> subroutine to apply the formats as follows (the code has been
> abbreviated here).
> 
> **********************************************************
> In my main procedure:
> 
> Dim MySheet as Excel.Worksheet
> 
> For i = 0 To myExcelApp.Worksheets.Count - 1
>     Set MySheet = myExcelApp.Worksheets(i + 1)
>     FormatSheet (MySheet)
> Next i
> 
> 
> My formatting sub:
> 
> Private Sub FormatSheet(sht as Excel.Worksheet)
>       sht.Select
>       Rows(1).Select
>       With Selection
>         .Font.Bold = True
>         .HorizontalAlignment = xlCenter
>       End With
> 
>       .... etc etc
> 
> End Sub
> 
> **************************************************************
> 
> Everything works fine if I put the formatting code right in the 'for'
> loop, but when I try to run it by calling the format sub from the
> loop, I get an 'Object doesnt' support this property or method' error
> on the 'FormatSheet(MySheet)' line.
> 
> Doug
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>