• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2224
  • Last Modified:

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
0
jefftwilley
Asked:
jefftwilley
  • 2
  • 2
1 Solution
 
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.  

-Jim
0
 
BigSchmuhCommented:
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...
0
 
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 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
0
 
BigSchmuhCommented:
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
0
 
Jim HornMicrosoft 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
    xlw.Save
Next
xl.Quit

'Close the recordset object.
rs.close

Hope this helps.
-Jim
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now