Excel formatting from Access

[tonyflavell]

I export a query from Access to an Excel sheet using "OutPutTo" and then use formatting commands to format the sheet. The problem is that the first time I export the query it remains unformatted, if I then repeat the export it formats perfectly however many times I run the query until Access is closed. Any ideas anyone?

Code used below:

+++++++++++++++++++++++++++++++++
Private Sub Export_Click()
+++++++++++++++++++++++++++++++++
On Error Resume Next

If IsNull(zOverUnderUsages) Then
MsgBox "No Data to display", , "No Data"
Exit Sub
Else
DoCmd.OutputTo acOutputQuery, "zOverUnderUsages", acFormatXLS, "C:\Program Files\Paper Database RT\OverUnderUsages.xls", True

Dim objXL As Object
Dim ExcelWasNotRunning As Boolean

Set objXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear

DetectExcel

Set objXL = GetObject("C:\Program Files\Paper Database RT\OverUnderUsages.xls")
objXL.Visible = True
objXL.Parent.Windows(1).Visible = True

'Formatting Commands here

End If
Set objSheet = Nothing
Set objXL = Nothing

End Sub

+++++++++++++++++++++++++++++++
Sub DetectExcel()
+++++++++++++++++++++++++++++++
Const WM_USER = 1024
Dim hWnd As Long

hWnd = FindWindow("XLMain", 0)
If hWnd = 0 Then
Exit Sub
Else
SendMessage hWnd, WM_USER + 18, 0, 0
End If

End Sub

+++++++++++++++++++++++++++++++
Declarations
+++++++++++++++++++++++++++++++

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As Long) As Long
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWind As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long

 

[RoyVidar]

I guess it might have to do with the difference of what you instantiate your object as:

Set objXL = GetObject(, "Excel.Application")
Set objXL = GetObject("C:\Program Files\Paper Database RT\OverUnderUsages.xls")

The first one, will instantiate objXL as an Excel application object - an existing open, running instance of Excel.

The second, I think would instantiate objXL as a Workbook object.

My way of doing this, could look something like this:

[tt]on error resume next
set xl=getobject(,"excel.application")
if err.number<>0 then
' no existing instance, create one
err.clear
set xl=createobject("excel.application")
if err.number<>0 then
' excel not installed?
exit sub
end if
end if
on error goto <your error handler>[/tt]

Roy-Vidar

 

[tonyflavell]

But presumably Excel has already been opened by the "OutputTo" query. So why dosn't the code connect to the existing instance of Excel the first time it is run and process the formatting commands correctly. The second and subsequent times (until Access is closed) it does, and formats correctly, time and time again?