[AccessD] Access/Excel formatting problem

Try This:

Private Sub FormatSheet(sht as Excel.Worksheet)
      sht.Select
      sht.Rows(1).Select
      With sht.Selection
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
      End With
      .... etc etc

End Sub

*** Untested ***

With Excel  automation I generally avoid the .Select

Something more like this:

Private Sub FormatSheet(sht as Excel.Worksheet)

      With sht Rows(1) 
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
      End With

      .... etc etc

End Sub

*** air code ***  Not able to test it at this time.


Boyd Trimmell 
aka HiTechCoach at UtterAccess.com and other support sites
Microsoft Access MVP 2010 ( MVP Profile )



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Steele
Sent: Sunday, January 23, 2011 1:46 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Access/Excel formatting problem

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