asked on

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?

J

Jim Horn

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.

-Jim

BigSchmuh

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...

jefftwilley

ASKER

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 header...it 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.
J

BigSchmuh

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.

hth

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial