Open Excel .xlsx file from Access

[Bigmix]

I had the following code that works perfectly for .xls files by running CmdOpenExcel () macro:



Private Sub CmdOpenExcel_Click()
Function CmdOpenExcel()
'Williford - opens the specified Spreadsheet
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Set xlApp = New Excel.Application
With xlApp
.Visible = True
Set xlWB = .Workbooks.Open("C:\Documents and Settings\Template.xls", , False)
End With
End Function

But when I change the file type to 2007 .xlsx or .xlsm (see below), the open command fails.

Set xlWB = .Workbooks.Open("C:\Documents and Settings\Template.xlsx", , False)


Can anyone help?

[gopherking]

BigMix, what was your solution on this? I have been having the same issue with opening any MS 2007 files from my Access 2007 DB, but it works just fine for MS 2003 or earlier files.

[Bigmix]

xlsm File for a template:

Option Compare Database
Option Explicit
Sub OpenSpecific_xlFile()
' Late Binding (Needs no reference set)
Dim oXL As Object
Dim oExcel As Object
Dim sFullPath As String
Dim sPath As String


' Create a new Excel instance
Set oXL = CreateObject("Excel.Application")


' Only XL 97 supports UserControl Property
On Error Resume Next
oXL.UserControl = True
On Error GoTo 0


' Full path of excel file to open
On Error GoTo ErrHandle
sFullPath = CurrentProject.Path & "\Scorecard Template.xlsm"


' Open it
With oXL
.Visible = True
.Workbooks.Open (sFullPath)
End With


ErrExit:
Set oXL = Nothing
Exit Sub

ErrHandle:
oXL.Visible = False
MsgBox Err.Description
GoTo ErrExit
End Sub

[Bigmix]

Hope that helps.

Here is the command button under Event > On Click

Option Compare Database
Private Sub cmdOpenExcelFIle_Click()
' Access form button
Call OpenSpecific_xlFile

End Sub

[gopherking]

Thanks for the post of your solution Bigmix ... unfortunately for me, I need something that will work for all types of files, not just Excel. I'm afraid that I may need to code for each specific MS Office 2007 file type and then treat all other files with a simple Application.FollowHyperLink. Thanks again and this may be helpful to someone else who is experiencing the same issue!

[gopherking]

Bigmix, I wanted to share with you the solution that I put in place for my database. Unfortunately, I was having issues where users couldn't open .docx or .xlsx files, so with your code above and assistance from some other threads I found, I came up with the code I posted in my original thread here. Just thought you may want to check it out. Thanks for your help.

[Bigmix]

Bigmix, I wanted to share with you the solution that I put in place for my database. Unfortunately, I was having issues where users couldn't open .docx or .xlsx files, so with your code above and assistance from some other threads I found, I came up with the code I posted in my original thread here. Just thought you may want to check it out. Thanks for your help.

That will come in very handy. Thank you for keeping me in mind.