Question about TransferSpreadsheet

[robojeff]

I am using the TransferSpreadsheet function to export a table to an excel spreadsheet but this only seems to allow me to place data on an individual worksheet.

I am updating my table with a query and would like to append the worksheet after each table update with the updated table information so that each update of the table ins displayed on the one spreadsheet but this method only seems to replace the total contents of the sheet...

Is there a way around this or a more appropriate way to do this?

Here is my command:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTemp, "C:\" & strFileName & ".xls", True, "All BOMs"

I can dump the contents to seperate worksheets if I change the "All BOMs" to an non-unique value after each update but need to append the one worksheet..


thanks

 

[Remou]

Have you considered linking the data from the Excel end?

http://lessthandot.com

 

[robojeff]

I am not sure how to link the data from the Excel end but I am creating the excel spreadsheet from access...

 

[Remou]

There is a tutorial:

http://office.microsoft.com/training/training.aspx?AssetID=RC011831161033&pid=CR061831141033

From what you say, it may be best to work from the Excel end, rather than the Access end.

http://lessthandot.com

 

[claudebo]

ditto to Remou advise.
I don't think you can specify which worksheet you are exporting to, but a linked sheets could be made to work.

 

[henniec]

Good day,
I normally do not use the Transferspreadsheet function but rather CopyFromRecordset function. You can specify where you want to place the data and if needed any formating can be done from within Access. I find this method easier to use.

Below is an answer I gave someone a few months ago. Try it and see if it helps.

I use a different method to export data from Access to Excel. I copy the whole table/query to the sheet with the steps indicated below. It is an extract of the method I use for all data exported to Excel and do most of the formatting from within Access.

If need you could trasfer the data to a different work sheet and then from within Excel append it to the table in Excel or do a row count via Access in Excel and use that number to append it from there.

Remember to synchronize your query with the columns in Excel if you copy directly into and existing table.

Hope this helps.

Hennie

Set objExcel = CreateObject("Excel.Application")
'Run qryMisaRSP and transfer data to sheet RSP for annual statistics.

Set rs1 = db.OpenRecordset("tblMisaRSP", dbOpenSnapshot)

'Set the object variable to reference the file you want to see.

With objXL
.Visible = True

Set objWkb = .Workbooks.Open(conWKB_NAME)

On Error Resume Next

Set objSht = objWkb.Worksheets(conSHT_NAME1) 'RSP
objWkb.Worksheets("RSP").Activate

objWkb.Windows("RSP").Visible = True

With objSht

'Copy data from the two record sets
.Range("A2").CopyFromRecordset rs1

 

[samiam07]

Transferring multiple tables/queries from Access to a single Excel workbook is something we do all the time. Here is a sample of the code to transfer 4 tables from Access to a single xls book called 'FileName'.

Dim SheetName1 As String
Dim SheetName2 As String
Dim SheetName3 As String
Dim Sheetname4 As String

SheetName1 = "tblOne"
SheetName2 = " tblTwo "
SheetName3 = " tblThree "
Sheetname4 = " tblFour "

DoCmd.TransferSpreadsheet acExport, 8, SheetName1, FileName, False, ""

DoCmd.TransferSpreadsheet acExport, 8, SheetName2, FileName, False, ""

DoCmd.TransferSpreadsheet acExport, 8, SheetName3, FileName, False, ""

DoCmd.TransferSpreadsheet acExport, 8, Sheetname4, FileName, False, ""

We then format each sheet in xls using Access VBA code.