VBA to refresh and publish Excel graphs (1 Viewer)

[MKaprielian]

Hello

Using Access 2007 or 2010

I have an Excel file containing links to an Access query.

The Access database process the data and the Excel spreadsheet graphs the data and publishes it a web page upon exit.

Currently my process to update the web pages produced by Excel is as follows:

--- 1 --- Import and process the data in Access
--- 2 --- Close the Access database because Excel requires it to be closed to update
--- 3 --- Manually open Excel to refresh the data (It is configured to refresh upon open)
--- 4 --- Manually close Excel to write the new graphs to their web location (it is configured to publish on close)

My goal is to have Access do all the work.

Is it possible to have Access VBA code cause Excel to do all the necessary things to publish the graphs?

It would seem me that Excel would have to provide a way to control it without the user interface in order to make this work.

If my goal is not achievable then at least opening the Excel file without locking issues would be useful.

Mark

 

[cpberg1]

I also use excel to publish some charts that I can't get in access.

The process that I use starts via macro in access.

1. run necessary queries
2. transfer spreadsheet command to export the data to my excel file
3. Run app command to open excel
4. Excel auto_open VBA to manipulate the data, create and export my charts to an outlook email, send the email, quit excel
5. Excel beforeclose VBA to clean up the excel sheet to prepare it for next time.

I'm not sure if it's the best way to go but runs seamless for me.

In your process description both of the programs are closed between step 2 and 3 which leaves nothing running process. Maybe a batch file can accommodate your applications?

 

[tehNellie]

I'd do it pretty much the way you describe cpberg.

Do the stuff in Access, have access open up Excel and push the data into it and call the necessary Excel things for excel to do what it needs to.

 

[MKaprielian]

Hi

Thank you both for your advice. Do you have sample code available that shows how to do this ?

Thanks
Mark

 

[cpberg1]

Created a quick sample for you to check out the logic. Place them both on your C drive in the C:\ . Open the Access database and click the run macro button.



Code will be a little difficult to provide beacuse it is very specific to the names of your forms, queries, excel file ect.

This should get you on the right path though.

 

[MKaprielian]

Thank you for the sample code. Been away on vacation, I look forward to seeing if I can make this all work.

Thanks
Mark