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!



On Mon, 10 Aug 2009 08:17:01 -0700, I.M. Shatner wrote:


Sub BOM()
'
Dim myobject As Object
Dim object As String
Myobject = "C:\dir\folder\filename"

Set oApp = myobject<<
oApp.Visible = True

oApp.cells.select

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

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" wrote:

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!



On Mon, 10 Aug 2009 08:17:01 -0700, I.M. Shatner wrote:


Sub BOM()
'
Dim myobject As Object
Dim object As String
Myobject = "C:\dir\folder\filename"

Set oApp = myobject<<
oApp.Visible = True

oApp.cells.select

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

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"


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

"Joel" wrote:

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" wrote:

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!



On Mon, 10 Aug 2009 08:17:01 -0700, I.M. Shatner wrote:


Sub BOM()
'
Dim myobject As Object
Dim object As String
Myobject = "C:\dir\folder\filename"

Set oApp = myobject<<
oApp.Visible = True

oApp.cells.select

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

For testing I added this line that needs to be deleted

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


"I.M. Shatner" wrote:

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"


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

"Joel" wrote:

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" wrote:

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!



On Mon, 10 Aug 2009 08:17:01 -0700, I.M. Shatner wrote:


Sub BOM()
'
Dim myobject As Object
Dim object As String
Myobject = "C:\dir\folder\filename"

Set oApp = myobject<<
oApp.Visible = True

oApp.cells.select

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

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" wrote:

For testing I added this line that needs to be deleted

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


"I.M. Shatner" wrote:

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"


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

"Joel" wrote:

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" wrote:

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!



On Mon, 10 Aug 2009 08:17:01 -0700, I.M. Shatner wrote:


Sub BOM()
'
Dim myobject As Object
Dim object As String
Myobject = "C:\dir\folder\filename"

Set oApp = myobject<<
oApp.Visible = True

oApp.cells.select

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

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" wrote:

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" wrote:

For testing I added this line that needs to be deleted

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


"I.M. Shatner" wrote:

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"


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

"Joel" wrote:

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" wrote:

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!



On Mon, 10 Aug 2009 08:17:01 -0700, I.M. Shatner wrote:


Sub BOM()
'
Dim myobject As Object
Dim object As String
Myobject = "C:\dir\folder\filename"

Set oApp = myobject<<
oApp.Visible = True

oApp.cells.select

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

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


"Joel" wrote:

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" wrote:

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" wrote:

For testing I added this line that needs to be deleted

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


"I.M. Shatner" wrote:

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"


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

"Joel" wrote:

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" wrote:

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!



On Mon, 10 Aug 2009 08:17:01 -0700, I.M. Shatner wrote:


Sub BOM()
'
Dim myobject As Object
Dim object As String
Myobject = "C:\dir\folder\filename"

Set oApp = myobject<<
oApp.Visible = True

oApp.cells.select

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

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

"I.M. Shatner" wrote:

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!



On Mon, 10 Aug 2009 08:17:01 -0700, I.M. Shatner wrote:


Sub BOM()
'
Dim myobject As Object
Dim object As String
Myobject = "C:\dir\folder\filename"

Set oApp = myobject<<
oApp.Visible = True

oApp.cells.select

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