Question about Do.Cmd TransferSpreadsheet

Posted on 2005-05-12
Last Modified: 2007-11-27
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?

Question by:jefftwilley
    LVL 65

    Expert Comment

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

    LVL 18

    Expert Comment

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

    Author Comment

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

    Expert Comment

    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.

    LVL 65

    Accepted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Suggested Solutions

    This article describes some very basic things about SQL Server filegroups.
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now