Access VBA to Excel Transfer

[zeeta999]

I want to output the results from an access query to an existing excel worksheet within a workbook. I do not want to delete the worksheet as another worksheet is tied in with formulas. I have no idea how to do this please help!

[Stewart Ross]

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 TransferSpreads heet 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