3

Currently I have an MS Access 2003 form with a combo box list of names. The way to use this tool: You select a name from the drop down and then click the MS Access Macro which runs (3 different queries) based upon name selected and then you open a master Excel Template and run an Excel Macro which will create 1 individual file for each salesperson.

What I am trying to do: Use MS Access VBA code to loop though the list of names in the combo box and then run the existing MS Access Macro to run each query from the list of names indiividually and then export each sales persons data to an excel Template which is already formatted or just to an Excel file and I will format.

I dont want to manually select a each name, a total of over 300 names from the drop down, it takes too long to run all of these queries. I want to use VBA code to loop through each name in the combo box on the form and use each name to run the existing ms access macro (which runs 3 queries together) and then I want to have the function export each individual file to Excel. The end result is that each salesperson will have his own Excel file.

Erik A
  • 31,639
  • 12
  • 42
  • 67
Weasy
  • 31
  • 1
  • 1
  • 2
  • 2
    Where do the names in the combo box come from? If from a query, can't you just run that query and loop through the records, running the reporting macro for each of them? Can you show any code (and can you modify the code in the form)? Simplest approach would be to loop through the entries in the combo box, setting the list selection to each item, and then run the reporting macro. Without a few more details it's difficult to offer more than that. – Tim Williams Mar 29 '11 at 20:38

2 Answers2

5
Dim i As Long
  with myCombo
    For i = 0 To .ListCount
        Debug.Print .ItemData(i)
    Next i
end with
iDevlop
  • 24,841
  • 11
  • 90
  • 149
1

Although the answer of iDevlop is technically correct, it would also give the Null value of the combobox at the end. So if you just want the listed items without null, use

Dim i As Long
With combobox
   For i = 0 To .ListCount - 1
       Debug.print .ItemData(i)
   Next i
End With
Mawimu
  • 11
  • 3