Run Excel Macro from Access

[Guest]

I would like to make some changes to the excel file before
I import the data into Access. I have a macro i excel
that I run ow to accomplish the task.

Can I run this macro from Access instead?

Thanks

 

[Guest]

no. i've wanted to do this for years.

 

[Jonathan Blitz]

You could cresate an Excel application object and open the workbook.

The exceute the command: Application.Run "Book1!Macro1"

This SHOULD work but I haven't tested it.

In general, when you want to know how to do something from Access in Excel
the best way is to record a macro in Excel doing whatever you wish to do and
then look at what is recorded. In this case I recorded the runnning of a
macro and this is what I got.

Hope that helps.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."

 

[Guest]

Thankyou. I will try it out ...

-----Original Message-----
You could cresate an Excel application object and open the workbook.

The exceute the command: Application.Run "Book1!Macro1"

This SHOULD work but I haven't tested it.

In general, when you want to know how to do something from Access in Excel
the best way is to record a macro in Excel doing whatever you wish to do and
then look at what is recorded. In this case I recorded the runnning of a
macro and this is what I got.

Hope that helps.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."



.

 

[Guest]

How do I create an Excel Application Object?

-----Original Message-----
You could cresate an Excel application object and open the workbook.

The exceute the command: Application.Run "Book1!Macro1"

This SHOULD work but I haven't tested it.

In general, when you want to know how to do something from Access in Excel
the best way is to record a macro in Excel doing whatever you wish to do and
then look at what is recorded. In this case I recorded the runnning of a
macro and this is what I got.

Hope that helps.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."



.

 

[Jonathan Blitz]

First you need to make sure Excel is avaiable in your access application.
In the VB editor choose Tools-->References and make sure that "Microsoft
Excel xx Object Library" is included - where xx is your version of Office (a
number)

In your code:


Global oXl As Excel.Application

Set oXl = CreateObject("Excel.Application")


add you code here (u can get all the code from creating a macro - even the
code for opening a workbook etc.)


oXl.Quit

the quit is required so as to close the Excel application.

Hope that helps


--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."