Excel Macro (1 Viewer)

[Anauz]

Hi

I am passing some data from Access to excel, so i can create a graph. I have a macro in excel where it creates the graph automatically. What i want to do is take the macro from excel and place it as code behind the button whic exports the data to Excel. Is it possible to perform functions in excel with code written in access?

 

[ctaylor]

I have not actually done this, but this looks like an interesting idea. Let me walk through and see if I can figure this one out.....

PART I - Binding Data to Excel
I opened EXCEL 2000 and created a new spreadsheet. Within this spreadsheet I selected the
DATA dropdown menu,
GET EXTERNAL DATA option,
NEW DATABASE QUERY sub-option

I tested this link by adding additional records to the Access database table then selecting

DATA dropdown menu
REFRESH option

The new data appeared in Excel. New Spreadsheet was saved as excellookup.xls on my "O" drive

PART II - Opening Excel from Access
I quickly placed a command button on a form and threw in a macro on the ON CLICK event. I specified the RunApp method with the command line instruction of

"d:\program files\microsoft office\office\excel.exe" "o:\excellookup.xls"

where the first section in quotation marks is the path to the Excel executible file on my local machine and the second item in quotes is the name of the path to the Excel spreadsheet created in PART I.

Clicking on the command button now opens the Excel spreadsheet with linked data.

================================
Please let me know how you resolve this matter. I am very interested to learn how to launch/print Excel graphs from Access.

[This message has been edited by ctaylor (edited 03-21-2001).]