vba help with macro

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

Hello,

I created two separate append queries to obtain the data and save the data
in the same table called "Output". I would like to create a macro to first
clear the existing data in the Output table, then run the two queries.
Lastly, either copy the data from the Output table and paste it to an Excel
file "BSchedule" in sheet1 starting A2 or refresh the data by importing in
the BSchedule. Any help is appreciated. Thanks
 
Cam said:
Hello,

I created two separate append queries to obtain the data and save the data
in the same table called "Output". I would like to create a macro to first
clear the existing data in the Output table, then run the two queries.
Lastly, either copy the data from the Output table and paste it to an
Excel
file "BSchedule" in sheet1 starting A2 or refresh the data by importing in
the BSchedule. Any help is appreciated. Thanks
Click to expand...


You say "macro", and you also say "VBA". In Access, unlike Excel, macros
are different from VBA procedures, so I'm not sure which you want. I'll
assume for now that you really do want VBA code.

The first part, emptying and reloading the table Output, is easy:

'----- start of example code -----
With CurrentDb

' Empty the Output table
.Execute "DELETE FROM Output", dbFailOnError

' Run first append query.
.Execute "FirstAppendQuery", dbFailOnError

' Run second append query.
.Execute "SecondAppendQuery", dbFailOnError

End With
'----- end of example code -----

For the second part, I don't know enough to be sure what you want. Does the
output Excel file already exist? Does it contain data you want to preserve?
We may be able to use the TransferSpreadsheet method, or it may be necessary
to automate Excel.
 
Back
Top