0

I'm using TransferSpreadsheet to export a query from access to excel and it works fine.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryName", "test1.xls", True

Now I've got another query and I want to add the data from this query to the same worksheet. How can I do this?

tutu
  • 673
  • 2
  • 13
  • 31
  • 2
    Try working with spreadsheet objects instead of DoCmd. See the third post here: http://www.pcreview.co.uk/forums/there-way-append-data-excel-file-t3803510.html – StuckAtWork Aug 10 '12 at 18:36
  • If your 2 queries are at all similar, you could also union them together, then use `DoCmd.TransferSpreadsheet` on the union query. – Scott Holtzman Aug 10 '12 at 18:39
  • What happens if you run the same command again using the second query ? Does it append the data or over-write ? – codegeek Aug 10 '12 at 18:48
  • @StuckAtWork Actually I'm using objects to edit the excel file's formatting and stuff after the TransferSpreadsheet. I think the difference between my code and the example you pointed out is the use of OpenRecordset to transfer data to excel instead of TransferSpreadsheet. Perhaps I can use OpenRecordset after TransferSpreadsheet for the second query to append the data to the same worksheet. – tutu Aug 10 '12 at 18:50
  • @codegeek It overwrites. – tutu Aug 10 '12 at 18:50
  • @Scott Holtzman it is pretty similar. But the reason I'm using two queries is because I want to edit the formatting of some of the rows between these 2 tables. So my idea was export the first query, edit the rows after the last row, append second query. – tutu Aug 10 '12 at 18:52
  • If the two queries are of the same parameters, could you create a temporary table in Access which has the first query UNION extra rows query UNION second query? eg `SELECT name FROM tbl WHERE id Is Not Null UNION SELECT 'next dataset' FROM tbl UNION SELECT name FROM tbl WHERE id Is Null` – StuckAtWork Aug 10 '12 at 18:59
  • 1
    @codegeek if you transfer two queries with the same name, it overwrites, if they have different names, they will be written to separate worksheets named for the query, eg "query1", "query2". I recently wrote about this on SO. In this case, I think automation would be the best bet. – Fionnuala Aug 10 '12 at 19:17
  • As you can see from all the comments there are several ways around it. If all you are doing is editing excel formatting, can't you do that after you transfer the UNION query? Or at worst, you can export two queries as @Remou has suggested, than do your editing and copy the 2nd sheet over after that, all in the XL object. – Scott Holtzman Aug 10 '12 at 19:26
  • I would not suggest a UNION. It is chancy in this case. The whole thing can be done with very little automation. – Fionnuala Aug 10 '12 at 19:30
  • 1
    Some notes: http://stackoverflow.com/questions/5043021/copying-data-from-a-ms-access-form-into-excel/5043171#5043171 – Fionnuala Aug 10 '12 at 19:34
  • I've made it work with CopyFromRecordset. @StuckAtWork You put me on the right track with that forumlink so if you reply I can mark your post as an answer. Thanks everyone for replying, I will post my code soon as example. Remou Thanks for that link as extra reference. – tutu Aug 10 '12 at 19:52

1 Answers1

4

For my first query I use

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNameFirst", "test1.xlsx", True, "MyWorksheetName"

For the second query I put it in a recordset

Dim rstName As Recordset
Set rstName = CurrentDb.OpenRecordset("qryNameSecond")

Then I just copy this recordset to the worksheet with CopyFromRecordset.

Dim objApp As Object, objMyWorkbook As Object, objMySheet As Object, objMyRange As Object

Set objApp = CreateObject("Excel.Application")
Set objMyWorkbook = objApp.Workbooks.Open("test1.xlsx")
Set objMySheet = objMyWorkbook.Worksheets("MyWorksheetName")
Set objMyRange = objMySheet.Cells(objApp.ActiveSheet.UsedRange.Rows.Count + 2, 1)

With objMyRange
 rstName.MoveFirst 'Rewind to the first record
 .Clear
 .CopyFromRecordset rstName
End With

objApp.ActiveSheet.UsedRange.Rows.Count will return the last used row number. I added + 2 because I want an empty row in between the two queries.

To add I did a performancetest. I tested this method with 500.000 records. The table containing 500k rows, the first query containing 250k rows, the second query (with the OpenRecordSet) containing 250k rows. It took about 10 seconds to generate the excel file sheet and display the data on a E6600 (2,40 Ghz), 4GB ram machine with access/excel 2010.

EDIT:

Another way to accomplish the same would be with using TransferSpreadsheet 2 times.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNameFirst", "test1.xlsx", True, "MyWorksheetName"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNameSecond", "test1.xlsx", True, "MyWorksheetName2"

This will create 2 Sheets in the workbook, then just copy the data of one worksheet to the other. I think the performance will be the same but not sure, I will stick with the OpenRecordSet.

tutu
  • 673
  • 2
  • 13
  • 31