Access - Excel - Access 2

[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.

 

[ejsmith]

Bryan, I'm not much of a programmer, but I have done similar things in the background without actually opening Excel using the following in Access VBA:

-OutputFileName is the name of the excel spreadshet you want to manipulate as a string... including the full path.

Dim X As Object

Set X = CreateObject("Excel.application&quot
X.workbooks.Open (OutputFileName)


X.Application.DisplayAlerts = False
X.Sheets("BackupData&quot.Visible = True
X.Run ("TheNameOfTheMacroYouWantToRun&quot
X.ActiveWorkbook.SaveAs OutputFileName
X.Sheets("BackupData&quot.Visible = False

X.Application.DisplayAlerts = True
X.Application.Quit
Set X = Nothing


Make sure you have set a refernce to the Excel object library in Access.

Hope this helps.

 

[ejsmith]

Bryan - ignore the lines referencing "BackupData" - thats a tab I use to store info in reports. I left those lines in by mistake!

 

[kupz]

Bryan,

I am not a VBA prgrammer expert but have used Access and Excel for a while now. Do the users need to use Excel as an interface? Can't this all be done in Access? Unless the user needs to do some data manipulation I think Excel is needed but I think in your case wouldn't Access be your only interface? It will eliminate a lot of your problems. But if you wish to do it this way that's fine. I do have some code that could run all you're macros in excel, I will just need to look for it.

 

[BMeek]

Thanks to everyone for responding. This was posted over a year ago and I have long since completed the project.

To myniguez: Yes, I used Access as the interface and just called on the reporting in Excel.

To ejsmith: The main problem I ran into was getting the hidden instance of Excel closed after I finished manipulating the data and printing the report. The secondary issue was allowing the user to save the report as a separate file.

If you are interested in my solutions, I will be happy to post a quick review. Let me know and again, Thanks for your suggestions.
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.

 

[Junior1544]

I love being able to find old messages that fix my problems! thanks ejsmith, that's the solution i needed

--James
JHauge@jmjpc.net
Life is change. To deny change is to deny life.