Code for opening Excel doc

[jenvandiver]

I'm trying to code a button on a form in Access to open an Excel template. This is what I've got so far. It works as far as opening Excel, but it won't open the file.

Private Sub CreateCommentsMatrix_Click()
Dim lngRetval As Long
Dim oApp As Object
Dim strDoc As String

lngRetval = MsgBox("Please wait..." & vbCrLf & "", vbOKOnly + vbInformation + vbDefaultButton1, "Creating Comments Matrix")
Select Case lngRetval
Case vbOK
End Select

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

With oApp
Set strDoc = ("C:\Documents and Settings\vandiverj\AMTPT\Training Tool\Templates\Comments Matrix.xlt")
OpenDocument (strDoc)

If Err Then
Shell "C:\Program Files\Microsoft Office\Office\" & "Excel/Automation", vbMaximizedFocus

AppActivate "Microsoft Excel"
End If

Set oApp = Nothing

End With

End Sub


I appreciate the help! Thanks,

 

[jenvandiver]

It's working now. This is the code I used...

Private Sub CreateCommentsMatrix_Click()
Dim lngRetval As Long
Dim oApp As Object
Dim strDoc As String

lngRetval = MsgBox("Please wait..." & vbCrLf & "", vbOKOnly + vbInformation + vbDefaultButton1, "Creating Comments Matrix")
Select Case lngRetval
Case vbOK
End Select

Set oApp = CreateObject("Excel.Application")
oApp.Visible = True

With oApp
.workbooks.Open "C:\Documents and Settings\vandiverj\AMTPT\Training Tool\Templates\Comments Matrix.xlt"

If Err Then
Shell "C:\Program Files\Microsoft Office\Office\" & "Excel/Automation", vbMaximizedFocus

AppActivate "Microsoft Excel"
End If

Set oApp = Nothing

End With

End Sub

 

[fuzzygeek]

Similiar way

Just another way to accomplish it also except you must have the Excel reference selected (Tools/Reference):

Function TestExcel()
Process_Begin:
'Dimension the application, the workbook, the worksheets

Dim objXL As Excel.Application
Dim wbXL As Excel.Workbook
Dim wsXLValues As Excel.Worksheet
Dim wsXLCCS As Excel.Worksheet


Process_Main:

' Create a new instance of Excel
Set objXL = New Excel.Application
Set wbXL = objXL.Workbooks.Open("C:\<Your Path>\<Your Excel spreadsheet>.xls")
Set wsXLValues = wbXL.Worksheets(1)
Set wsXLCCS = wbXL.Worksheets(2)
objXL.Visible = True
Set wsXLValues = Nothing
Set wsXLCCS = Nothing
Set wbXL = Nothing
Set objXL = Nothing
End Function

 

[jenvandiver]

Thanks. It's working great at this point as far as opening my template, HOWEVER, I had created the template to run a database query on open, which is does if I open the template directly. But now that I'm opening it through Access, it won't automatically refresh the data. It opens up a new worksheet, but it's blank until I manually re-query. Is this an easy fix?