It has been a long while since I've tried to automate Excel from Access.
Originally Posted by
archy321
If I wanted multiple queries to go into the same excel form <snip>
This is not clear to me. Do you want to add data from multiple queries to the SAME worksheet ("Sheet1") in one workbook?
Or add multiple worksheets to one workbook?
How many queries?
Originally Posted by
archy321
<snip> make a module for each query<snip>
No!! Depending on what you want, all of the code for multiple queries could/would be in one procedure.
Speaking of which, what is the purpose of the button? A button name of "Command0" is not very descriptive. I ALWAYS change the name Access gives objects. For example, if I have a list box for employees, Access names the list box (when it is created) something like "List3". I rename it to "lstEmployees".
In the IDE, which tells you more: "Private Sub List3_Click" or "Private Sub lstEmployees_Click"??
Looking at you code, you really should learn to add comments. (I did ).
In 6 months, you are going to look at the code and say <head_slap>"What was I thinking of?" (been there, done that)
Now that I have had to decipher the code, I have questions.
Why do you use
Code:
Set MyQueryDef = MyDatabase.QueryDefs("April deviations")
Set MyRecordset = MyQueryDef.OpenRecordset
when you could just use
Code:
Set MyRecordset = MyDatabase.OpenRecordset("April deviations")
You don't do anything with the query def..........
You have
Code:
Set wb = xlApp.Workbooks.Open("C:\Users\archy\Documents\fli ght metrics.xlsx")
but you don't use it.
After you copy the data from the recordset (and put it in the sheet starting at cell A41), why do you then add the header row (field names) at cell A1?
Ha! Didn't see all those questions coming, did you?