GetObject and CreateObject not behaving

[doctorswamp]

Hi

Want to send data to Excel but keep getting multiple instances of Excel starting if I use

GetObject("","Excel.Application") in the code below.

Without the first double quote I get Error 429 and debug highlights the GetObject line.

Any help much appreciated.

***************

Dim objXL As Object
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
If Err <> 0 Then
Set objXL = CreateObject("Excel.Application")
End If

objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\ics.xlt")
etc

 

[kjv1611]

Have you thought about using the DoCmd.TransferSpreadsheet function instead? It's very straitforward, uses little code, and is possibly the easiest way to export tables and queries to Excel.

 

[doctorswamp]

Thanks kjv.

The method I'm using allows me to export cell by cell and implant anywhere into an Excel template, ie not as rows. As I understand it, the TransferSpreadsheet function takes data row by row. Is this so?

 

[doctorswamp]

Postscript : I've found that this does work on another computer, whereas the first computer returned a 429 error. Stepping through the code it doesn't get beyond the line

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

so the error trap can't operate.

Any ideas?

 

[Remou]

However, you are getting an error? You mention error 429 - so it seems that it is Resume Next that is the problem. Have you tried using On Error GoTo? Which version of Access are you getting the problem with?

Here is some API stuff from Microsoft:

Option Compare Database

' Declare necessary API routines:
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 hWnd As Long, ByVal wMsg As Long, _
               ByVal wParam As Long, _
               ByVal lParam As Long) As Long

'Other variables
   Dim MyXL As Object   ' Variable to hold reference to Microsoft Excel.
   Dim ExcelWasNotRunning As Boolean   ' Flag for final release.

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
   Const WM_USER = 1024
   Dim hWnd As Long
' If Excel is running this API call returns its handle.
   hWnd = FindWindow("XLMAIN", 0)
   If hWnd = 0 Then   ' 0 means Excel not running.
      Exit Sub
   Else
   ' Excel is running so use the SendMessage API
   ' function to enter it in the Running Object Table.
      SendMessage hWnd, WM_USER + 18, 0, 0
   End If
End Sub

 

[doctorswamp]

Thanks Remou

It's Access 2003.

Did you mean this as an alternative to Resume Next? It doesn't get beyond the Set objXL line on Step Into and gives the same error 429.

***********

On Error GoTo Flag
Set objXL = GetObject(, "Excel.Application")
Flag:
Set objXL = CreateObject("Excel.Application")

***********

Also wasn't sure what to do with your API code.

 

[Remou]

Ok, that was not very clear. I meant On Error GoTo as an alternative to Resume Next, with CreateObject used in the error handler. The API stuff is a different way of detecting Excel, it is instead of GetObject(, "Excel.Application"). As it says in the comments, the API returns a handle, if Excel is running. I am just suggesting a few things to try, to see if they help, I'm afraid.