Loop to run query for store then save table as excel file

[bmiller469]

I feel like this should be pretty easy and routine, but I'm just not as familiar w/ VBA as I'd like to be.

I have a list of store numbers in an excel file.
1) I need to copy the first store number from an excel file
2) Paste it into the "Store Number" criteria of query 01
3) Run the query macro (about 5 queries)
4) Save the final table as an excel file w/ store number as the file name
5) Go to the store number in the row directly below the prior one, and do it all over again.

Any assistance w/ the coding and automation of this process would be greatly appreciated.

 

[SkipVought]

Hi,

"I have a list of store numbers in an excel file.
1) I need to copy the first store number from an excel file "

Then why not do it ALL in Excel? Use MS Query via Data > Import Extern Data > New Database query... to run the Access queries and return the resultset to a sheet in Excel.

If you choose to pursue this option, please repost in forum707. You'll get lots of VBA Excel help.



Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[bmiller469]

It would be easier for me to import the excel file as a table in access than to remake all my queries in MS Query.

Should I re-post in the forum you recommended?

 

[SkipVought]

"... remake all my queries in MS Query"

Why remake anything. If you have a query in Access, you can use that query as if it were a table.

Just another option.

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}