Bug opening Excel spreadsheet from Access

[Guest]

I am using the following code, which I have copied from Access VBA Help on
GetObject, to open a spreadsheet from Access but when the spreadsheet opens
the screen freezes or an Excel.exe error is generated.

The declaration section is not included in Help and initially I got an error
with the code 'Sub or function not declared' so I found the code below on
another website and that stopped that problem:

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lParam As Any) As Long

Sub GetExcel()
Dim MyXL As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.

' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXL = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel

' Set the object variable to reference the file you want to see.
Set MyXL = GetObject("c:\Myfile.xls")

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True

' ...
' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
'If ExcelWasNotRunning = True Then
' MyXL.Application.Quit
'End If

'Set MyXL = Nothing ' Release reference to the
' application and spreadsheet.
End Sub

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

Thanks for any help

 

[Ken Snell [MVP]]

The code that you've posted is a bit overkill for most situations.

Here is some sample code to get you started:

Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
On Error Resume Next
Set xlx = GetObject( , "Excel.Application")
If Err.Number <> 0 Then Set xlx = CreateObject("Excel.Application")
' Comment out the next line if you don't want EXCEL to be "seen" by user
xlx.Visible = True
Set xlw = xlx.workbooks.Open("C:\Filename.xls"), , False
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
'
' do other things here
'
Set xlc = Nothing
Set xls = Nothing
xlw.Save
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing

 

[Guest]

Ken

It all looked a bit over the top so thanks for your simpler code.

Regards