How to run queries in Access and populate Excel worksheet with results
Hi Everyone,
I'm just starting to learn how to code with VBA for my new job. Last week, I picked up two texts on programming with VBA. One text is all about programming with VBA for Excel, and the other is all about programming with VBA for Access. I plan on thoroughly reading both books over the next several months, but a project that I'm currently working on requires some more advanced ad hoc programming that I need to take care of within the next month or so. I've quickly browsed through both texts to see if there are any sections explicitly detailing how to integrate the two applications using VBA, but it's been of no avail; hence, this post. Here's what I'd like to do:
Create a worksheet containing some parameters that are to be selected by whoever uses the workbook (this part's simple), and then, in that worksheet, store a button underneath the parameters section labeled 'Print', that, when clicked after selecting the given parameters, prints out a series of documents associated with the parameters selected. I understand that I can easily do this using a Do-Until statement, having the macro scan through the records of a table of data and selecting the values of the fields in every record (as parameters that populate each document being printed) in which the selected parameters are present. For example, suppose I have a drop-down list on the first worksheet for Parameter1, where the user can select either '1', '2' or '3', and the user chooses '2', and then the user clicks 'Print'. I'd like the macro to then scan through a table created by querying in Access such as:
| Parameter1 |
Parameter2 |
Parameter3 |
| 1 |
100 |
1 |
| 2 |
102 |
2 |
| 1 |
101 |
3 |
| 3 |
100 |
4 |
| 2 |
101 |
5 |
Then I'd like the macro to print two documents, one populated by the set of parameters, (2, 102, 2), and then a second one populated by the set of parameters, (2, 101, 5).
The only part of this macro I'm going to have difficulty coding is the part where I run the query in Access and then take the resulting table and insert the table into a second worksheet in Excel for the macro to scan through, row by row. If someone could elaborate on how I could actually code the process of running the query and inserting the results into Excel or provide some example code, it would be GREATLY appreciated. Thank you!
