Run Excel macros from Access?

[M Skabialka]

My users will be getting a new Excel workbook every two weeks, and the info
will be imported into Excel. I have created macros in the Excel workbook to
create new worksheets with the data formatted so that it can be imported
directly into an Access table. However, every two weeks there will be a
replacement workbook, so the macros would have to be copied to the new one.
I can see this as a disaster waiting to happen when they delete the old one,
macros and all.

Can all of the instructions in the macros for the Excel workbook be run from
Access? They select ranges, transform and copy them to a couple of new
worksheets where all formatting is removed. Access will then import the
tables, and do some data cleanup.
Mich

 

[Clif McIrvin]

Disclaimer: User with limited experience.

I see two choices (at least)

1. Put the macros into another location (Personal.xls;
MacroForExport.xls, whatever) so they won't "accidently" get deleted. If
necessary, put a "link" macro in Personal.xls to launch your macro(s).

2. Put the macro VBA behind a form in Access and use COM Automation to
manipulate the Excel object.

Do a search in the archives for discussion regarding Excel Automation.

HTH!

 

[Chad Cameron]

Could you have an addin that has a menu for the user to click on, or
whatever to run the macro which would also be in the addin? If the marco is
always the same, couldn't it be in the template of the workbook that is
always replaced?

 

[M Skabialka]

Of course, why didn't I think of that - a second workbook and the original
can be opened read-only so no changes will be made to it.
I will look into the Excel Automation also
Thanks,
Mich

 

[Clif McIrvin]

That's why these forums are here ... so we can help each other!

Glad my thoughts were of value <g>

--
Clif

Of course, why didn't I think of that - a second workbook and the
original can be opened read-only so no changes will be made to it.
I will look into the Excel Automation also
Thanks,
Mich

 

[Guest]

My users will be getting a new Excel workbook every two weeks, and the
info will be imported into Excel. I have created macros in the Excel
workbook to create new worksheets with the data formatted so that it can
be imported directly into an Access table. However, every two weeks there
will be a replacement workbook, so the macros would have to be copied to
the new one. I can see this as a disaster waiting to happen when they
delete the old one, macros and all.

Can all of the instructions in the macros for the Excel workbook be run
from Access? They select ranges, transform and copy them to a couple of
new worksheets where all formatting is removed. Access will then import
the tables, and do some data cleanup.
Mich

 

[Ken Snell [MVP]]

'********************************
'* 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

 

[M Skabialka]

The workbook comes from an outside source - our users find it difficult to
find the information they need in it, hence the database creation. If there
is a template, it would not contain my macro.

 

[M Skabialka]

This has been an exciting project - I have created macros in Excel to pull
data from the original workbook into another workbook, while reorganizing
the data. I then run code from Access to run the Excel macros, then to pull
the revised data into Access tables and organize that into normalized data.
So each time the user gets a new workbook, he just pushes a button in Access
and all data is transferred and ready in under a minute. His original
workbook remains unchanged.
Thanks for your assistance,
Mich

 

[JHB]

'********************************
'* Call anEXCELmacrofrom 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

Mr Snell:

I am using the code you posted, and have modified it thus:

Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "InvestmentPriceUpdateProcess.xls"
strMacro = "Auto_open"
Set xls = CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("Z:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Function

Sadly I am getting an Error 440 (automation error) on the line:
xls.Run strFile & "!" & strMacro

Strangely, the process appears to work however. If I remove that
line, the process does not work.

Do you have any suggestions to overcome this error?

Thanks

John Baker

 

[Ken Snell [MVP]]

I've not seen that error before, and cannot think of why it should occur
with the code. However, we can trap it and not have it show:

Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
On Error Resume Next
strFile = "InvestmentPriceUpdateProcess.xls"
strMacro = "Auto_open"
Set xls = CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("Z:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Function


You're not saving the changes made by the macro in your workbook when you
close the workbook file. Is that what you intended? If you want to save the
changes, change this line:

xwkb.Close False


to this:

xwkb.Close True
--

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

'********************************
'* Call anEXCELmacrofrom 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

Mr Snell:

I am using the code you posted, and have modified it thus:

Dim xls As Object, xwkb As Object
Dim strFile As String, strMacro As String
strFile = "InvestmentPriceUpdateProcess.xls"
strMacro = "Auto_open"
Set xls = CreateObject("Excel.Application")
xls.Visible = True
Set xwkb = xls.Workbooks.Open("Z:\" & strFile)
xls.Run strFile & "!" & strMacro
xwkb.Close False
Set xwkb = Nothing
xls.Quit
Set xls = Nothing
End Function

Sadly I am getting an Error 440 (automation error) on the line:
xls.Run strFile & "!" & strMacro

Strangely, the process appears to work however. If I remove that
line, the process does not work.

Do you have any suggestions to overcome this error?

Thanks

John Baker