Open/Run Excel file & Macro from Access 1

[BMeek]

I have a project that uses Access and Excel to graph results of a query. My problem is binding between Access and Excel. I can get the data from a query input into the Excel file, but then I want to open the Excel file, run a couple of macros to allow the user to input their name and company into Input boxes. Then copy the data into the correct cells to update the charts inside a one-page report. In the Access program, my users choose a value from a city or zip code field using a drop box. Then they click a button to create the report.

In Access, my code uses an export table as static data to the Excel file. The data is transferred with no problem.

In Excel, I have to manually open the file to run my macros to:
1) Open an input box for the user to enter their name. Their name is saved into a cell that formats into the final report page.
2) Open a second input box for the user to enter their company name - same process.
3) A third macro copies the imported data into the pre-arranged chart cells to update the two graphs on my one-page report.
4) Print the one-page report to the default printer
5) Save the file and close Excel

I am trying to get everything automated so the user will not have to manually open Excel and run the macros. I want Excel to open in the background, remain unseen, run the macros and print the document. My problem is when I get Excel to open using GetObject, I can't get the macros to run. I have tried copying the macros from Excel into my Access modules, but they still don't run. The best I've gotten so far is, Access opens the Excel file, The Excel macros do not run, Excel saves the file, and the close process hangs because the macros have not run.

Does anyone have a suggestion for getting the Excel macros to run properly? Do I need to rethink the process?

Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A "Solution Provider" in your corner gives you the personal attention you need to find the right technology solutions for your business.

 

[RickSpr]

What method are you using to run the macros? Are you getting a trappable error that you're ignoring?

Also, could you use "Dim xl as Excel.Application" instead of instantiating with GetObject()? GetObject causes IDispatch binding, while the other method usually uses vTable binding. IDispatch binding might involve getting DCOM set up properly, which is messy.

 

[BMeek]

I've been attempting to use:
DoCmd.RunMacro ("GetData&quot
Set MyXL = Sheets("ZipReport&quot.PrintOut
DoCmd.Save
where "GetData" contains all of my macros and MyXL is my variable for the Excel file. The macros have been in the Excel file, do you think I should move them to Access and reword them appropriately?

In the meantime, I will attempt using your suggestion for "Dim MyXL as Excel.Application" and see what I get.

I have tried using DDE to connect to Excel, but I can't get a channel to connect at all.
Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A "Solution Provider" in your corner gives you the personal attention you need to find the right technology solutions for your business.