run-time error 1004 when trying to open an excel spreadsheet from access

  • Thread starter Thread starter Bryon
  • Start date Start date
B

Bryon

Hi,

I'm attempting to open an excel workbook from access and run a macro.
I can open the workbook fine by hand and I know that path is correct.
Each time the code executes I get:

Run-time error ‘1004': Cannot access ‘import.xls'.

I found one article from Microsoft that tells about a security change
in excel allowing VBA projects to execute a macro and I have already
made this change.

If anyone could help I would greatly appreciate it.

Private Sub Refresh_Data_Click()

Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "import.xls"
strMacro = "Update"
Set xls = CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("E:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing

End Sub
 
Bryon,

Your code executes on my machine without errors. It even
runs my macro and my macro security is set to "Medium" in
Excel.

Perhaps some other application is locking your Excel
file. Have you tried restarting your machine and then re-
running your code (*without* running other apps first)?
 
Back
Top