Excel macro in Access module

[I.M. Shatner]

I thought this would be an Access question because this module is in Access -
but I was directed to post at the Excel board.

I have an OutputTo macro in Access which creates an excel file. I want a
formatting macro to run after a Call command in that OutputTo macro. When I
run that one it creates the file then stops at the line in the 2nd module in
between arrows below. Is there a better way to have the Excel formatting
happen
automatically after the OutputTo macro?

I have Office 03 and Windows XP.

Thanks!

 

[Joel]

I ran this code from access on one of my workbooks and it runs without any
errors

Option Compare Database

Sub BOM()

Dim FName As String
Dim myobject As Object
Dim object As String
FName = "C:\dir\folder\filename"
FName = "C:\temp\book1.xls"


Set oApp = GetObject(FName)
oApp.Application.Visible = True

With oApp.Sheets(1).Cells

With .Font
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
.Rows.AutoFit
.Columns("A:A").Font.Bold = True
End With
End Sub

 

[I.M. Shatner]

Thanks for helping. I'm using your code but I get a runtime error 432 at the
line in the arrows. Any ideas?


Sub BOM()

Dim FName As String
Dim myobject As Object
Dim object As String
FName = "I:\dir\folder1\folder2\file"
FName = "I:\temp\book1.xls"

oApp.Application.Visible = True

 

[Joel]

For testing I added this line that needs to be deleted

FName = "I:\temp\book1.xls"

 

[I.M. Shatner]

I'm still getting the same error at the same spot. The file exists. I even
tried saving it on a different drive and using that path, but it's still
stopping me with 432 at the Set oApp line.

 

[Joel]

You don't have a file called : "C:\dir\folder\filename"

You are missing the extension XLS. Make sure the file exists.

 

[I.M. Shatner]

That is frakin AWESOME! Thank you so much my friend.

 

[Barb Reinhardt]

You need to define the XLApp and an XLWB

This is untested

Sub BOM()
'
Dim myobject As Object
Dim object As String
Dim XLWB As Object
Dim XLWS As Object
myobject = "C:\dir\folder\filename"
Set oApp = CreateObject(, "Microsoft Excel")
oApp.Visible = True

'Here you need to use an XLWB
'Not sure if you want to open something or add to one that exists
'But here are some ideas
'
Set XLWB = oApp.Workbooks.Add 'Adds a workbook
Set XLWS = XLWB.Worksheets(1) 'Chooses the first worksheet
XLWS.cells.select
With XLWS.Selection.Font
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
XLWS.Selection.Rows.AutoFit
XLWS.Columns("A:A").select
XLWS.Selection.Font.Bold = True
XLWS.Range("A1").select
End Sub