I need help with creating a complicated Access Query from Excel

[Bullpen]

Hey everyone. I have no experience writing any VBA or Macros in Excel. I need to perform a very specific function to get an Access query into Excel. What I need to do is below:

1. I need to have a button on the Excel spreadsheet that will start the act of pulling info from Access
2. I need to COPY an Access file from a location on a shared drive, and paste it into a specific folder
3. I then need to open that copied Access file
4. Once the Access file is open, I need it to automatically run a specific pre-loaded (or saved) query
5. I then need that info to be input into an Excel spreadsheet (currently, when you run the saved Access Query, a pop-up asks if you want to wait while it sends it to Excel, and then it automatically sends it into Excel)

So any help you guys can offer will be GREATLY appreciated! Please feel free to ask my any clarifying questions.

John

 

[chuckles1066]

Hey everyone. I have no experience writing any VBA or Macros in Excel. I need to perform a very specific function to get an Access query into Excel. What I need to do is below:

1. I need to have a button on the Excel spreadsheet that will start the act of pulling info from Access
2. I need to COPY an Access file from a location on a shared drive, and paste it into a specific folder
3. I then need to open that copied Access file
4. Once the Access file is open, I need it to automatically run a specific pre-loaded (or saved) query
5. I then need that info to be input into an Excel spreadsheet (currently, when you run the saved Access Query, a pop-up asks if you want to wait while it sends it to Excel, and then it automatically sends it into Excel)

So any help you guys can offer will be GREATLY appreciated! Please feel free to ask my any clarifying questions.

John

For 3 - 5 you can do something like

Set db = CreateObject("Access.Application")
db.Visible = False
db.OpenCurrentDatabase (dbloc)
db.DoCmd.RunMacro "mcr_import_data"

where dbloc is the location of the database you are opening and mcr_import_data is a macro that does what you need from within Access.

 

[Bullpen]

Thanks Chuckles1066. So for the "mcr_import_data" part, do I need to make a macro for that in excel or in Access? I assume Excel.

How do I do that in Excel? Can I just use 'Record Macro' to make it or do I need to program it?

Thanks for your help!

John

 

[chuckles1066]

Thanks Chuckles1066. So for the "mcr_import_data" part, do I need to make a macro for that in excel or in Access? I assume Excel.

How do I do that in Excel? Can I just use 'Record Macro' to make it or do I need to program it?

Thanks for your help!

John

"mcr_import_data" is calling a macro by that name that exists in your Access database.

Basic macros in Access need no VBA knowledge.