Code to open specific Excel sheet ...

[m.cringle]

Hi

Can anybody tell me what code I need to just open an Excel spreadsheet?

I already have an event sorted but I'm not sure how to get the
spreadsheet to open afterwards. I'm new to the VBA 'stuff'!

Cheers guys!

 

[Guest]

First make sure you have a reference to the Excel object library in your
database (Tools|References on the VBA menu bar). Then add a module along
these lines to the database:

''''module starts''''
Option Compare Database
Option Explicit

Private Function GetExcelApp() As Object

' if Excel open return reference to it
' else establish reference to it
On Error Resume Next
Set GetExcelApp = GetObject(, "Excel.Application")
If Err.Number = 429 Then
Set GetExcelApp = CreateObject("Excel.Application")
End If

End Function


Public Sub OpenWorkbook(strWorkBook As String)

Dim appExcel As Excel.Application

Set appExcel = GetExcelApp()

appExcel.Workbooks.Open FileName:=strWorkBook
appExcel.Visible = True

End Sub
''''module ends''''

Call the OpenWorkbook procedure, passing the path to the Excel file to it,
e.g.

OpenWorkbook "F:\SomeFolder\SomeSubfolder\SomeWorkbook.xls"

Ken Sheridan
Stafford, England

 

[m.cringle]

Great stuff - thanks Ken, much appreciated

First make sure you have a reference to the Excel object library in your
database (Tools|References on the VBA menu bar). Then add a module along
these lines to the database:

''''module starts''''
Option Compare Database
Option Explicit

Private Function GetExcelApp() As Object

' if Excel open return reference to it
' else establish reference to it
On Error Resume Next
Set GetExcelApp = GetObject(, "Excel.Application")
If Err.Number = 429 Then
Set GetExcelApp = CreateObject("Excel.Application")
End If

End Function


Public Sub OpenWorkbook(strWorkBook As String)

Dim appExcel As Excel.Application

Set appExcel = GetExcelApp()

appExcel.Workbooks.Open FileName:=strWorkBook
appExcel.Visible = True

End Sub
''''module ends''''

Call the OpenWorkbook procedure, passing the path to the Excel file to it,
e.g.

OpenWorkbook "F:\SomeFolder\SomeSubfolder\SomeWorkbook.xls"

Ken Sheridan
Stafford, England