add row to excel sheet

[Cowboy_BeBa]

hi everyone

so far i havent worked out all the details but what i need to do is find a way to automatically copy data from an access query to a sheet in excel

at the end of every month we record financial data from our database into an excel sheet for our accountants. im still designing the query to format the data exactly the way we need it, which is why im not too clear on all the details (have a meeting with the accountants on wednesday to confirm everything, but im hoping to get most of the function written by then at least)

Ive already got the path\filename and worksheet name stored in variables (excelFile and workSheet respectively) and ive worked out a way to store the row number (in a variable called simply rowNum), i just need to add one record to that specific row, from columns B to I) and for now lets just assume the query would be qryFinancialData
am hoping theres a simple way to export the data, ill be formatting the query so that the first field in the query will go into column B, second field will be C, third field will be D, etc.

is there a quick way to open the file, add the data to the right columns/row and close/save the excel file, all through VBA?

 

[Isskint]

Hi Cowboy

The function you are looking for is DoCmd.TransferSpreadsheet. One of the parameters will allow you to specify where to put the data EG

DoCmd.TransferSpreadsheet acExport, 8, "qryFinancialData", pathToSpreadsheet, True, "Tab!RANGE"

where Tab is the tab name and RANGE the range/cell to paste to. You could build this as a variable.

Another option would be to set up a data connection between the Excel sheet and the Access query. This way you just update the data table in Excel.

Another alternative would be automation, code that opens the spreadsheet and transfers your data row by row (or incorporate the clipboard and copy the data to it).

 

[CJ_London]

with transferspreadsheet, range only applies to importing, not exporting so not applicable in this instance.

you will need to use excel automation - see this link for some ideas

http://bytes.com/topic/access/answers/879680-automating-data-export-import-access

Or alternatively, set up a link in Excel to the query

 

[wiklendt]

If your accountants have access to the same server space, the other alternative is to allow the accountants to have access directly to your database. Create a button for them to press so the database will show them what they want to see.