P
Patrick
I have the following code to run an excel macro contained in a excell 2003
file, and when i try to run this code froma ccess, it tells me the macro does
not exist, what am i doing wrong, i can run the macro from excell ok.??
i refer to the comple file path and file name in variable strWrkBk and just
the macro name in variable strProc
Public Function RunExeclMacro(strWrkBk As String, strProc As String)
'Important Note: The Sub/Function.... must be made Public to be
'able to run it remotely!
Dim xlApp As Object
Dim xlWrkbk As Object
Dim d As String
' On Error Resume Next
' Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance
of Excel
' If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo RunExeclMacro_Error
Set xlApp = CreateObject("excel.application")
' Else
On Error GoTo RunExeclMacro_Error
' End If
Set xlWrkbk = xlApp.Workbooks.Open(strWrkBk)
xlApp.Visible = True 'make Excel visble to the user
d = strWrkBk & "!" & strProc
'd = strProc
xlApp.Run d 'Run the Procedure
xlWrkbk.Close (True) 'saves any changes that occurred do to the Proc
Running
xlApp.Quit
Set xlWrkbk = Nothing
Set xlApp = Nothing
If Err.Number = 0 Then
RunExeclMacro = True
Exit Function
End If
RunExeclMacro_Error:
If Err.Number = 1004 Then
'Workbook/Procedure not found
MsgBox "Invalid Workbook name or Procedure Name", vbCritical
Else
MsgBox "MS Access has generated the following error" & vbCrLf &
vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: RunExeclMacro" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured!"
End If
RunExeclMacro = False
Exit Function
End Function
file, and when i try to run this code froma ccess, it tells me the macro does
not exist, what am i doing wrong, i can run the macro from excell ok.??
i refer to the comple file path and file name in variable strWrkBk and just
the macro name in variable strProc
Public Function RunExeclMacro(strWrkBk As String, strProc As String)
'Important Note: The Sub/Function.... must be made Public to be
'able to run it remotely!
Dim xlApp As Object
Dim xlWrkbk As Object
Dim d As String
' On Error Resume Next
' Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance
of Excel
' If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
On Error GoTo RunExeclMacro_Error
Set xlApp = CreateObject("excel.application")
' Else
On Error GoTo RunExeclMacro_Error
' End If
Set xlWrkbk = xlApp.Workbooks.Open(strWrkBk)
xlApp.Visible = True 'make Excel visble to the user
d = strWrkBk & "!" & strProc
'd = strProc
xlApp.Run d 'Run the Procedure
xlWrkbk.Close (True) 'saves any changes that occurred do to the Proc
Running
xlApp.Quit
Set xlWrkbk = Nothing
Set xlApp = Nothing
If Err.Number = 0 Then
RunExeclMacro = True
Exit Function
End If
RunExeclMacro_Error:
If Err.Number = 1004 Then
'Workbook/Procedure not found
MsgBox "Invalid Workbook name or Procedure Name", vbCritical
Else
MsgBox "MS Access has generated the following error" & vbCrLf &
vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: RunExeclMacro" & vbCrLf & _
"Error Description: " & Err.Description, vbCritical, "An Error has
Occured!"
End If
RunExeclMacro = False
Exit Function
End Function