Hi. In a response I made to a previous poster (post #6 of
this thread) I provided two VBA routines which together allowed the poster to transfer data from Access to a pre-existing Excel workbook template (or any other Excel workbook). This same code can be used in your case, substituting the name of your file for the template name. You need to make one small addition: when you open the workbook you have to select the sheet into which you want the data copied. This is done by adding a new line at line 21 of the TransferSpreadsheet method in post 6 above:
- ...
-
end if
-
objExcel.Worksheets("name of your worksheet").activate ' << add this line
-
TransferQueryData Tablename, objExcel
-
...
if you do not have an existing blank sheet in your workbook into which you want to copy the data you will need to add a new worksheet instead. This is done by changing the worksheet selection line (line 3 above) to one that adds a worksheet:
- objExcel.ActiveWorkbook.Worksheets.Add Before:=objExcel.Worksheets(1)
Either way, all existing sheet references for other worksheets are not altered in any way by copying the Access data to a blank existing sheet or into a new worksheet.
-Stewart
4
