Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: Transfering Data

    What is the best/Fastest way to transfer data from Access to Excel? I have a database in which a query returns approx. 20,000 records. I would like to send this to excel but have found that the transfer is slow. Any suggestions? I have use the Transferspreadsheet Method and I have a module that will transfer the data also:

    Set db = CurrentDb
    Set objXL = CreateObject("Excel.Application")
    objXL.Visible = True
    objXL.Application.Workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkbook

    strSQL = "SELECT Invoice_Matching.* FROM Invoice_Matching;"

    Set rs = db.OpenRecordset(strSQL, 2)

    With objActiveWkb
    .Worksheets(1).Cells(1, 1) = "Supplier Name"
    .Worksheets(1).Cells(1, 2) = "Ticket"
    .Worksheets(1).Cells(1, 3) = "Ticket Date"
    .Worksheets(1).Cells(1, 4) = "Invoice Number"
    .Worksheets(1).Cells(1, 5) = "Invoice Total"
    .Worksheets(1).Cells(1, 6) = "Deferred Taxes"
    .Worksheets(1).Cells(1, 7) = "Invoice Minus DT"
    .Worksheets(1).Cells(1, 8) = "Invoice Date"
    .Worksheets(1).Cells(1, 9) = "Invoice Due Date"
    .Worksheets(1).Cells(1, 10) = "Supplier Terminal"
    .Worksheets(1).Cells(1, 11) = "Invoice Received"
    .Worksheets(1).Cells(1, 12) = "COMETS Terminal"
    .Worksheets(1).Cells(1, 13) = "COMETS Total Amount"
    .Worksheets(1).Cells(1, 14) = "COMETS Supplier"
    .Worksheets(1).Cells(1, 15) = "COMETS Release Number"
    .Worksheets(1).Cells(1, 16) = "COMETS Purchase Contract"
    .Worksheets(1).Cells(1, 17) = "Exception Status"
    .Worksheets(1).Cells(1, 18) = "COMMENTS"
    .Worksheets(1).Cells(1, 18) = "Person Assigned"

    Row = 2
    While rs.EOF <> True

    .Worksheets(1).Cells(Row, 1) = rs![Supplier Name]
    .Worksheets(1).Cells(Row, 2) = rs![Ticket]
    .Worksheets(1).Cells(Row, 3) = rs![Ticket Date]
    .Worksheets(1).Cells(Row

    ... you get the idea. These two methods are too slow. Any help?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why not set it up as a query, then export the entire query using transferspreadsheet? It works fairly quickly for me...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2006
    Posts
    9
    try using docmd.outputto

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    In my experience, the code method is the slowest, especially if Excel is visible.
    Using a a built in "Export to Excel" has proven to be the fastest for me.
    I have to agree with Teddy and scripts82.

    How much memory you have (both physical and virtual) will affect the export speed for larger sets of data. Bottom line is, 20K records is more than a few and patience may be your only option when dumping to Excel.

    tc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •