How to cause a spreadsheet (Excel) to run from an Access Macro

[JHB]

Hi:

I need to program an Access Macro (Access 2000) so that it causes an
XLS spreadsheet to function. The spreadsheet is already operational,
macro driven, and designed to gather data from a number of sources ,
presenting the results as a coherent record. I will then import the
results (Spreadsheet import) into an Access table for further use.

The question, then is: What macro command do I use to open an Excel
spreadsheet from Access?

Thanks in advance

John Baker

 

[Ken Snell [MVP]]

Here is sample VBA code for how to run an EXCEL macro from ACCESS:

'********************************
'* Call an EXCEL macro from VBA *
'********************************

Public Sub RunAnExcelMacro()
Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "ExcelFilename.xls"
strMacro = "MacroName"
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Sub

 

[JHB]

Here is sample VBA code for how to run an EXCEL macro from ACCESS:

'********************************
'* Call an EXCEL macro from VBA *
'********************************

Public Sub RunAnExcelMacro()
Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "ExcelFilename.xls"
strMacro = "MacroName"
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Sub

Thank you for the response. I gather there is no normal access command
(such as "run.macro" or "run.code) that will do this? Im afraid I am
not VBA literate, but if this is the only way to do it I will try and
make it work. Don't feel I am not grateful for your response, I am.
Its just that I am looking for a real simple way to do it. The
Spreadsheet can easily be set to execute the macro when it opens, so
all I really need to do is be able to open a spreadsheet from Access.

Thanks again

John Baker

 

[ZigZagZak]

Ok, just make a command button on your form & put this on its on click event.

Dim oApp As Object
Dim oWkBook As Object
Dim oSheet As Object
Set oApp = CreateObject("Excel.Application")
oApp.Visible = False
oApp.displayalerts = False
Set oWkBook = oApp.Workbooks.Open("S:\SHARED\ACCESS RAW MATERIALS\BAR COUNT
BLANK.xls")

The part that it looks like a file path *IT IS* so change it to the file
path for your excel document.

Hope this is what you were searching for!

Zach

 

[Ken Snell [MVP]]

ACCESS does not have built-in commands/actions to automate code that exists
in an EXCEL file. You need to run VBA code to automate the EXCEL file and
then do things to it -- such as running a VBA macro that is in the EXCEL
file. This example code is the simplest way I know for running an EXCEL
macro from ACCESS.

As noted by ZigZagZak, you can call the provided VBA code by the Click event
of a command button on a form. Or you can have a macro attached to the On
Click property of a command button call the VBA code.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Here is sample VBA code for how to run an EXCEL macro from ACCESS:

'********************************
'* Call an EXCEL macro from VBA *
'********************************

Public Sub RunAnExcelMacro()
Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "ExcelFilename.xls"
strMacro = "MacroName"
Set xls= CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("C:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Sub

Thank you for the response. I gather there is no normal access command
(such as "run.macro" or "run.code) that will do this? Im afraid I am
not VBA literate, but if this is the only way to do it I will try and
make it work. Don't feel I am not grateful for your response, I am.
Its just that I am looking for a real simple way to do it. The
Spreadsheet can easily be set to execute the macro when it opens, so
all I really need to do is be able to open a spreadsheet from Access.

Thanks again

John Baker