Formatting Data on Export

[alsoascientist]

Hi All,

I have set up a code that will export information from a query based on conditions described by functions within the code and I am now trying to take hold of Excel (the information is exported to there)and format the data.

I have seen a coulpe of ideas kicking about on the net but they all opening the database at the start - mine is already open. I also have the issue that the file name (workbook name) will be set by the user on export, so I don't have the option to close and then re-open.

I have this so far, and I am getting the error "automation error, invalid syntax"

'Export code is in here...
 
DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, , True
 
Dim ObjExcel
Dim ObjSheet
   Set ObjExcel = GetObject(Excel.Application)'this is where the error happens
   ObjExcel.Visible = True
 
   Set ObjSheet = ObjExcel.ActiveWorkbook.Worksheets("qryTemp")
 
   With ObjSheet
    ActiveWindow.DisplayGridlines = False
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection.Interior
        .ColorIndex = 11
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
'there is a lot more formatting code but it didn't see the use in putting it here too

Any ideas / advice?

 

[spikepl]

When you get an error, then that is not time to panic but to sit down and think.

Set ObjExcel = GetObject(Excel.Application)

is syntactically correct, so what could it be?

The only bit you cannot see in this statement is what is the data type of ObjExcel. The name implies an object, but is it?

 

[alsoascientist]

errrrmmm.... yes? no?

I really don't know. What do you mean by data type? I have never tried to use access to control other applications so am somewhat out of my depth on this one! It seems to work when opening a predefined file so where I am looking? The GetObject part or the part I am trying to describe as an object?

 

[alsoascientist]

I thought I should update this as there have been no further responses - I haven't been able to get this resolved, however I have managed to do a similar thing as a workaround where I save the file as a set name in a set location and then reopen it and set the new file as read only. Its' not perfect but it kinda does the job. Code below if anyone has been watching this thread.

'Exports the information
    DoCmd.OutputTo acOutputQuery, "qryTemp", acFormatXLS, _
    "XXX.xls", False
'Sets up the new instance of Excel
    Set oXLApp = New Excel.Application
    Set oXLBook = oXLApp.Workbooks.Open("XXX.xls")
    Set oXLSheet = oXLBook.Worksheets(1)
'Sets the new file as read only
    oXLBook.ChangeFileAccess Mode:=xlReadOnly