Automating importing data from access into excel into multiple fields

[joeray1983]

Hello,

This topic may have been touched on here before, but as I am new to the forums please bear with me.

I have designed a query in access to pull information from a database.

However since this report will be used regularly, I wanted to automate the process by using VBA. Any suggestions on how to code this in VBA if I wanted to pull information from multiple queries within the database, and have that information populate into specific cells in excel?

Any help would be greatly appreciated.

Cheers,

Joe

 

[Smitty]

Welcome to the Board!

Excel has a built-in tool to pull data from external sources. IN 2007+ goto Data-->From Access & follow the wizard. You will be prompted for where you want to put the query result(s).

In earlier versions goto Data-->Import External Data-->New Database Query & follow the wizard.

Once you have it set up you can set a refresh interval, have it refresh on open or refresh it from VBA.

HTH,

 

[joeray1983]

Smitty,

Thanks for the quick response! I considered this option, however the person requesting the report wants it to be pretty much hands free report generation. As in: Insert a button in excel, click it, and viola! Theres your information concerning the project that the queries are mining the data for. So unfortunately I dont think this will work. Any other suggestions?

Thanks Again,

Joe

 

[Smitty]

Well,

You can record a macro running through the steps I outlined above. VBA will capture all of the correct path information for you.

If you build a list of available queries, you can prompt the end-user to select one from the list, and ask them to provide the cell address where they want the result(s).

E.G.

Destination:=Range(UserPrompt/Selection)).QueryTable
.CommandText = Array(UserPrompt/Selection)