Access loop

[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.

1) I have a list of store numbers in an excel file, which I can import as a table in Access, if necessary.
2) I need to copy the first store number from the list
3) Paste it into the "Store Number" criteria of query 01
4) Run the query macro (about 5 queries that are pretty complicated)
5) Save the final table in a pre existing excel file w/ vlookups and forumlas already in place. Saved as the store number as the file name.
6) Go to the store number 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,

add querytable via Data > Import External Data... ONE TIME to query your access db.

Turn on your macro recorder and record EDITING the query and returnig data to excel.

Your recorded code will be the basis for the query loop.

Make a store nbr list as a NAMED RANGE.

From the store nbr list

dim r as range
for each r in Range("StoreNbrList")
   sSQL = "Select .... Where [Store Number]='" & r.value & "'"
   with yoursheetobject.querytables(1)
      .connection = yourconnedtstring
      .commandtext = sSQL
      .Refresh BackgroundQuery:=False
   end with
Next

Skip,

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