Automate Process

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

 

[GGW]

You might try writing VBA code in your workbook:
"ThisWorkbook" object can have an auto open subroutine -

Private Sub Workbook_Open()

...programming stuff including
yoursubroutine 1
yoursubroutine 2
yoursubroutine n...

end sub

 

[blakex1]

GGW has the right idea for automating the macros in EXCEL.
However it seems a bit easier to create the charts in Access and attach them to the report. If that isn't an option...
It is possible to open an application in VBA code,
once the application (EXCEL) is opened it will look for any "on open" code attched to the workbook and run that for you automatically.

 

[JerryDennison]

I would have to agree with blakex1, don't use Excel at all. Create the charts as a report in Access. What chart type is it?

 

[BMeek]

Thanks for all the suggestions, I have been able to solve the initial problems and am on to the "clean-up" of code and making the program secure.

FYI: How I did it
I used a variation of this procedure to open Excel, then open the specific report file I want to print. After getting the program to open, then run the series of modules to get the user's name and company for the report, copy the data to the graph ranges, update the charts, and print the one-page report; I removed the xlApp.Visible = True because I don't want the user to see what is going on. I just want them to make their choices from my form in Access, click the report button, then everything happens behind the scene.


Sub AutomateExcel()
Dim xlApp As Excel.Application

' Create new hidden instance of Excel.
Set xlApp = New Excel.Application
' Show this instance of Excel.
xlApp.Visible = True
Stop ' Press F8 to continue stepping through code.
With xlApp
' Code to automate Excel here.
End With
xlApp.Quit
Set xlApp = Nothing
End Sub

The only problem I have right now, is that the instance of Excel that is opened does not close out of memory when the code is finished running.

I appreciate the suggestions to keep everything in Access, but the graph and chart functions are not well documented and I do not have the time to "hit and miss" while trying to get the report generated in Access. My client provided our company with a sample report in Excel, so I used it as a template to create my program.
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.