Solved: GetObject not working

[tebule]

:banghead:

O.K. I have been working on this for a few hours so here is my problem:

I created an application that imports .csv file information and then outputs an excel document. That last part is where I am having the trouble. I create an excel document using:


DoCmd.OutputTo acOutputQuery, "Output_NoNAIC", acFormatXLS, _
strMonthlyFileCompPath, True


The true swith on the code above opens an instance of excel with my new workbook. What I am trying to do is set an object to my new workbook so I can format my output. I have tried:


Dim objXL As Excel.Application
'AppActivate "Microsoft Excel"
Set objXL = GetObject(strPathName, Excel.Application)
' When Window Name stored in variable can then activate by
' this function.
' Example below:

''Set objXL = Excel.Application
'Application.Workbooks.Worksheets(1).Visible = _
'DLookup("ShowExcel", "tblControls")

And a few other things that I have deleted. From what I am reading, I should be able to use the GetObject function. The commented out lines are the ones that I tried and gave up on. Any help is greatly appriciated. If I don't figure this out I will have to turn off the switch and use the createobject function. I would rather learn the getobject function instead.:wot

And If I have to use an API, how do I declair them?

Any help appricated!
Thanks,
Bre

[Oorang]

Early Bound
Step One: From the tools menu, select references, and set a reference to Microsoft Excel.
Step Two: (See Below)
Dim xlApp as Excel.Application
Set xlApp = New Excel.Application
xlApp.Visible = True
Late Bound

Dim xlApp As Object
Set xlApp = VBA.CreateObject("Excel.Application")
xlApp.Visible = True

Get Current Instance
Dim xlApp As Object 'Or Excel.Application
On Error Resume Next
Set xlApp = VBA.GetObject(, "Excel.Application")
If Err Then
'do early or late bound "New Object" method instead.
End If
On Error GoTo 0

[tebule]

This is what I finally came up with:
Public objXL As Object
Dim objXLSheet As Object

Set objXL = GetObject(strPathName)
objXL.Application.Visible = DLookup("ShowExcel", "tblControls")
objXL.Parent.Windows(1).Visible = DLookup("ShowExcel", "tblControls")
'Manipulate the worksheet
Set objXLSheet = objXL.ActiveSheet
With objXLSheet.Rows("1:1")
.Font.Name = "Arial"
.Font.Size = 7
.Font.Bold = True
.WrapText = True
.RowHeight = 45
End With
objXLSheet.Range("1:1").AutoFilter


Now I am having problems with the close.

I found ways to close only my workbook and to close all of excel, but if there are two workbooks excel closes both of them instead of just my workbook and also the excel process does not close and I can still see excel.exe in my task/process window. Thoughts?


I though of doing an if statement and checking if there was more then one book or one book when it closes and doing two different close statements dependent on the results. But I am hoping that there is a better way.

[Oorang]

Well when you do it that way, you are actually getting a workbook object. So just use the close workbook syntax:
objXL.Close False
Moo