Data export to Excel

[LEXCERM]

Hi all,

I would like to export results from an Access query and append it to existing data in a spreadsheet. I do not want to export the headers from Access either.

Also, if a spreadsheet has several different tabs, can you define which tab the data can be appended to?

I would like to combine the following code to achieve the result:-

DoCmd.TransferSpreadsheet acExport, 8, "QUERY1", "C:\Documents and Settings\BOOK1.XLS", FALSE
Sheets("Sheet2").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

How far am I off the beaten track?!?

Regards.

 

[The_Doc_Man]

You have the idea but started wrong. You don't use TransferSpreadSheet when you want the code to do the updates for you at the level of detail you are suggesting.

Here is how I would do it. First, open an Applications object for Excel.

Using the Apps object, open a workbook. You can see a collection of worksheets in the workbook. Worksheets.Name is the name of the nth worksheet.

Select the right worksheet. You can see a collection of rows AND a collection of columns. But DON'T use both at the same time without a lot of good Scotch handy. Worksheets.Rows(m) (or ...Columns(m) ) selects the row or column (no DUH there).

Within a Worksheet, you can use a collection of Rows for which the columns are in a collection called Cells. Oddly enough, if instead you choose to use the collection of Columns, the rows are in a collection called.... wait for it... CELLS.

So the problem is to open the workbook, open the worksheet, select a row, and load the cells of the row from your recordset. You should also look at the .Text property in Excel's Help on its VBA interface. Because each Cell has many properties including colors, fonts, contents, borders, etc. etc. The text in the cell is only one of many properties of the cell. To make your life easier, give yourself a reference to Excel using the module page's Tools >> References dialog box. Then you can define some temporary items like an Excel.Row as a holder for a collection of pre-loaded cells. Look up the term ActiveSheet, which is a built-in shortcut somewhat similar to the "ME" construct used in the VBA code of some forms.

Oh, one last thing. If you open it, close it. If you allocate it, release it. If you created it but don't need it any more, delete it. Yeah, I know - sounds like normal programmer warnings. But for application objects, this last bit of advice is unusually important if you don't want to start seeing memory errors.

 

[LEXCERM]

The Doc Man,

Thank you very much for your concise and very informative reply. I understand VBA in excel as I use it every day so I totally understand what you are saying with regards to ranges/cells/columns etc. However, I am at the novice stage when it comes to Access VBA so I am a bit lost with the beginning part of your reply i.e. "open an Application object for Excel". Do I do this in Access?

If I give you a set of events, could you possibly construct the code for me (at least the beginning part)....

1) Using "QUERY1"
2) Open worksheet "BOOK1.xls"
3) Select sheet tab "SHEET1"
4) Find the last record in column "A" and offset by one row down.
5) Append data from Access query.

Thanks again for your help. Regards.