Access export excel file with macro help

[shockerfest]

Hello there, I am new to these forums and in search of some help.

I am currently working on a project that involves running a macro off access to export an excel file with the appropriate queries. So far I have been successful in doing that. The next step is where I am stuck, well a couple of things.

1. Is there a way to automatically open your exported excel file after running your macro off access?

2. After the export is complete all of the data is arranged how I want on 2 worksheets. However, I need a way to enable a macro to automatically format the information (which I have created, but don't know how/where to save it appropriately). But the thing is, since the database is being constantly updated, and I need to export information from that database several times a day, and the formatting macro needs to be applied everytime I export this file (which isn't a problem if I was the only user). Also, other people are exporting the same information but without the formatting because they don't have the right formatting macro that I have created. Is there a way to integrate the formatting macro I have created to this specific file for every user that tries to export this same file?

[Gollem]

Hi,

to open an excel:

 
Dim AppExcel        As Object
 
    Set AppExcel = CreateObject("Excel.application")
 
    AppExcel.Workbooks.Open "c:\test.xls"
    AppExcel.Visible = True

You can create the macro at runtime, never done it before myself.

Another idea is instead of creating everytime a new excel-file, use a template instead. This means, you create an empty excel-file with some macro's, formatting, ... and you save it as a readonly file for other users.

When exporting the data you fill in this file and save it as another file. This method I use often.

[shockerfest]

Hi Gollem,

Thanks for the VBA code, makes me feel that much more lazy (not having to find the file)

The template idea seems pretty straight forward but I want to challenge myself

I really like the runtime idea. The VBA code has got me thinking. Since access can open up an excel file after export. Shouldn't there be a way to somehow execute some code (obviously after the open excel code) to save the format macro into the "ThisWorkbook" macro tool of excel?

Either that or just straight up execute after the excel file has been opened?

Thanks in advanced