Open excel spreadsheet in access

[Jimmy P]

I hope this is as simple as it sounds, I would like to know how to open an
excel spreadsheet from a Switchboard and/or a Form.

Thanks

 

[Klatuu]

It is not hard, but neither is it simple. How much VBA experience do you have?

 

[Jimmy P]

I've done some programming with VB6. Let it rip dave....

 

[Klatuu]

Okay,here is the code to open an application, a workbook, and select a
specific worksheet:

On Error Resume Next ' Defer error trapping.
Me.txtStatus = "Opening Spreadsheet"
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
'Open the Workbook
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)
Set xlSheet = xlBook.Worksheets("Actuals_res_export")
************************************************

Here is how you close it:

xlBook.Close
Set xlBook = Nothing
Set xlSheet = Nothing
'If we createed a new instance of Excel
If blnExcelWasNotRunning = True Then
xlApp.Quit
Else
xlApp.DisplayAlerts = True
xlApp.Interactive = True
xlApp.ScreenUpdating = True
End If
Set xlApp = Nothing

***********************************
And the sub called during the open process:

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
*****************************************

Good luck.

 

[Jimmy P]

Thank you. I will keep you posted on my progress.

 

[Jimmy P]

I'm getting an error on the FindWindow statement. Is this supposed to calla
sub routine?

Thanks

 

[Dale Fye]

Jimmy,

You don't really need the DetectExcel call in there, so you can either
remove it or remark it out.

Also, if you actually want to see the spreadsheet after it is opened, you
will have to set its visible property to true

Set xlSheet = xlBook.Worksheets(....)
xlApp.Visible = true

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.