I need help with creating a complicated Access Query from Excel

Bullpen

New Member
Joined
Jul 19, 2011
Messages
45
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

chuckles1066

Banned
Joined
Dec 20, 2004
Messages
372
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

Code:
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

New Member
Joined
Jul 19, 2011
Messages
45
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

Banned
Joined
Dec 20, 2004
Messages
372
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,783
Messages
5,446,479
Members
405,403
Latest member
horace james

This Week's Hot Topics

Top