Access VBA Automation Controlling Formatting in Excel

[DCPan]

Hi,

I found this forum via google search. I've read the previous post on formatting in Excel via Access, but still couldn't get this to work.

Here's my code:

Private Sub cboExcelMacro()

Dim objXLApp As Object
Dim objXLBook As Object
Dim objXLSheet1 As Object
Dim objXLSheet2 As Object
Dim objXLSheet3 As Object

Set objXLApp = CreateObject("Excel.Application")
Set objXLBook = objXLApp.Workbooks.Open("C:\Documents and Settings\DPan1\Desktop\Tester\Test02.xls")
Set objXLSheet1 = objXLBook.Worksheets("qry_Check_Remit")
Set objXLSheet2 = objXLBook.Worksheets("dbo_EDI_WM_DFIs_to_be_Created_v")
Set objXLSheet3 = objXLBook.Worksheets("qry_InvoicesToBeCleared")

objXLApp.Application.Visible = True

objXLSheet1.Activate

objXLSheet1.Range("A1:M1").Select
objXLSheet1.Selection.Font.Bold = True <- fails right here
objXLSheet1.Range("A:M").Select
objXLSheet1.Selection.Columns.AutoFit
objXLSheet1.Range("C:J").Select
objXLSheet1.Selection.EntireColumn.Hidden = True

objXLSheet2.Cells("A11").Select
objXLSheet2.Selection.Font.Bold = True
objXLSheet2.Columns("A").Select
objXLSheet2.Selection.Columns.AutoFit
objXLSheet2.Columns("C:J").Select
objXLSheet2.Selection.EntireColumn.Hidden = True

objXLSheet3.Cells("A1:M1").Select
objXLSheet3.Selection.Font.Bold = True
objXLSheet3.Columns("A:M").Select
objXLSheet3.Selection.Columns.AutoFit
objXLSheet3.Columns("C:J").Select
objXLSheet3.Selection.EntireColumn.Hidden = True

Exit_Code:

objXLBook.Save
objXLBook.Close
objXLApp.Quit
Set objXLSheet3 = Nothing
Set objXLSheet2 = Nothing
Set objXLSheet1 = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing
Exit Sub

End Sub


---

So, it fails up there where I try to change my selected area to font bold...what am I doing wrong?

Thanks!

 

[PHV]

Any error message ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

You may try this:
...
objXLApp.Application.Visible = True
objXLSheet1.Range("A1:M1").Font.Bold = True
objXLSheet1.Range("A:M").Columns.AutoFit
objXLSheet1.Range("C:J").EntireColumn.Hidden = True
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

The problem is combining the sheet object & Selection which implies the sheet object...

    [s]objXLSheet1.[/s]Selection.Font.Bold = True '<- fails right here

But I suggest avoiding Activate & Select methods...

    objXLSheet1.Range("A1:M1").Font.Bold = True
    objXLSheet1.Range("A:M").Columns.AutoFit
    objXLSheet1.Range("C:J").EntireColumn.Hidden = True

Skip,

_[red][/red]

 

[DCPan]

Hi PHV,

Access just told me the selection doesn't make sense.

SkipVought, your changes worked! Thanks a million! So I guess using the Macro Recorder in Excel and just pasting the VBA code over and adding the 'objectname.' in front of the Excel VBA won't work.

Thanks again for everything you guys! You've made my day!

TGIF!