Question about Do.Cmd TransferSpreadsheet

I don't know if this questions is worth any points, and it may be a yes or no....but here goes

Is it possible using MS Access2000 to use the TransferSpreadsheet command to start writing data to a specified row? Ex: Row 14.

I am using xlWs.Cells(R,C) = rst!(name)   now. It works, but its very slow.

Any thoughts from you guys?

LVL 34
Who is Participating?
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Here's the full template...

Dim xl As New Excel.Application
Dim xlw As Excel.Workbook

xl.Workbooks.Open ("Your Excel Spreadsheet")

rs.Open "Whatever", , adOpenStatic, adLockReadOnly

xl.ActiveSheet.Range("Some Named Range, or top left of range like A1").CopyFromRecordset rs

'Save and quit the Excel file.
For Each xlw In xl.Workbooks

'Close the recordset object.

Hope this helps.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Docmd.TransferSpreadsheet will only export to a sheet starting at a1.  If you need to paste data to specific cells, you'll need to go with your approach.  

You can use ADO over a link to your Excel file through ODBC xls driver but I don't know if Access allows this usage...
jefftwilleyAuthor Commented:
The ADO method was tried already by a previous designer...and because of the nature of the template spreadsheet and the linking issues I came across because of a would have never worked (even though he told the boss it did.....then left). I'm ironing out the bugs with the cell by cell method and it is working ok so far. The boss is happy.  I'll leave this question open a while longer for additional comments.
Clearly, both methods (ADO through ODBC and TransferSpreadsheet) works only if your Excel sheet is column oriented (no header no column starting lines after the first one)

Do you reference your xlWs application object using
 DIM xlWs as Excel.Application
or using
 DIM xlWs as Object
==> The performance difference can be x5...because the first one is tightly coupled instead of using a dynamic link...

Otherwise, you can although use a macro to format your xls to a column oriented file...That will be faster too if you have more than few dozens lines.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.