Getobject: ActiveX Component Can't Create Object Error

[okitamura]

Anyone,

I have been trying to output one of my quesries from Access using OutputTo method to diplay the content in Excel Spreadsheet which works just fine. After that I am trying to format Excel Spreadsheet(Coloring etc). What I dot not understand is sometimes it works when I step thru but sometimes it generates a Run Time Error 429: ActiveX Component Can't Create Object. I looked thru previous posting regarding this issue but do not understand why my application is behaving this way. Could you possibly tell me how I can resolve this so that I do not have to see this annoying error ever again? I would appreciate any feedback. Thank you in advance!


DoCmd.OutputTo acOutputQuery, "Output",acFormatXLS, strfilename, True


Dim xlApp As Object 'Excel.Application

Set xlApp = GetObject(, "Excel.Application")

' Formating worksheet
xlApp.Rows.Font.Name = "Arial"
xlApp.Rows.Font.Size = 8
xlApp.Rows.Font.Strikethrough = False
xlApp.Rows.Font.Superscript = False
xlApp.Rows.Font.Subscript = False
xlApp.Rows.Font.OutlineFont = False
xlApp.Rows.Font.Shadow = False
xlApp.Rows.Font.Underline = xlUnderlineStyleNone

'Autofit all rows
xlApp.Rows.AutoFit

'Formating first row
xlApp.Rows("1:1").Select
xlApp.Rows("1:1").HorizontalAlignment = xlCenter
xlApp.Rows("1:1").VerticalAlignment = xlCenter
xlApp.Rows("1:1").WrapText = False
xlApp.Rows("1:1").Orientation = 90
xlApp.Rows("1:1").AddIndent = False
xlApp.Rows("1:1").ShrinkToFit = False
xlApp.Rows("1:1").MergeCells = False
xlApp.Rows("1:1").Font.Bold = True

'Formating the columns
xlApp.Columns("A:B").Select
xlApp.Columns("A:B").EntireColumn.Interior.ColorIndex = 15
xlApp.Columns("C:L").Select
xlApp.Columns("C:L").EntireColumn.Interior.ColorIndex = 39
xlApp.Columns("M:Q").Select
xlApp.Columns("M:Q").EntireColumn.Interior.ColorIndex = 15
xlApp.Columns("R:W").Select
xlApp.Columns("R:W").EntireColumn.Interior.ColorIndex = 36
xlApp.Columns("X:X").Select
xlApp.Columns("X:X").EntireColumn.Interior.ColorIndex = 15
xlApp.Columns.AutoFit
'Saving application
xlApp.Application.DisplayAlerts = False
xlApp.Application.Save
xlApp.Application.DisplayAlerts = True

' Header formating

xlApp.ActiveSheet.PageSetup.PrintArea = ""
xlApp.ActiveSheet.PageSetup
xlApp.ActiveSheet.LeftHeader = ""
xlApp.ActiveSheet.CenterHeader = " & cstr(count) & "
'xlApp.ActiveSheet.LeftMargin = Application.InchesToPoints(0.75)
'xlApp.ActiveSheet.RightMargin = Application.InchesToPoints(0.75)
'xlApp.ActiveSheet.TopMargin = Application.InchesToPoints(1)
'xlApp.ActiveSheet.BottomMargin = Application.InchesToPoints(1)
'xlApp.ActiveSheet.HeaderMargin = Application.InchesToPoints(0.5)
'xlApp.ActiveSheet.FooterMargin = Application.InchesToPoints(0.5)
'Application close
xlApp.Application.Quit

' Release objects
'Set xlApp = Nothing

 

[lupins46]

Set xlApp = GetObject(, "Excel.Application")

This statement will grab an open Excel session.
It will not create a new one if Excel is not running.

You can test for an error immediately following this statement; if there is an error then use CreateObject to start a new XL session.

 

[PHV]

DoCmd.OutputTo acOutputQuery, "Output",acFormatXLS, strfilename, True
Excel SHOULD be opened.
Try using DoEvents just after the OutputTo call.

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