Access to Excel WITHOUT 'Data' sheet VBA

[alexmb1]

I am currently running a VBA script that imports my Access 2007 table into Excel. I have set it up to import to a sheet in my workbook called 'Data'. In the main sheet, where the calculations are done, I use a sumproduct formula to find and calculate values from the 'Data' tab. In this way I can preserve the standard formatting that is used in the report I am generating.

I would like to find a way in VBA to export the Access data directly into my Excel template without having to store the data in a separate sheet. When I diseminate the report I do not want the data visible to the viewers. I also do not just want to 'hide' the sheet.

Is there a way to do this in VBA? Also, I am looking for something relatively simple. I have heard rumors that there is a simple few lines of code I can add to achieve this.

Thanks,
Alex

 

[boblarson]

It really depends on what you mean by exporting it directly into your Excel Template and what the Excel Template looks like in relation to your data. You can use Excel Automation code to do almost anything and place anything anywhere you want but how complex it is depends on your data, where you want it, if you want it in non-contiguous columns/rows, etc.

 

[alexmb1]

The data from access all goes into the same column but in non-contiguous rows. There are spaces between the rows to make the report easier to read. So for when I describe expenses for Program 'X', the values go into A1, A3, A5, A7 etc.

 

[alexmb1]

Also, because there is a "notes" section, sometimes the rows are more than 1 row apart. thus, it would be difficult to just use a 'row+1' code to move to the next cell and input data.