Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: SLOOOW VBA data transfer from Access to Excel sheet

    Hello everybody,

    I have a pretty large database (~50.000 rows x 10 cols). The data is saved as a table with Access. A query is called, which sorts the data by date (1st column). This query is called from a Access-VBA routine and the VBA code writes all data into an Excel sheet. This process takes ~1 hour to complete .

    I'm desparately trying to improve the performance here, and at least cut the processing time by 50%. Are there ways to make the following lines faster?

    .
    .
    set rstVal = qdf.OpenRecordset(dbOpenSnapShot)
    .
    .
    For i = 0 To numRows
    set rngCurrent = rngABeg.Offset(i, 0)
    For j = 0 to rstVal.Fields.Count - 1
    rngCurrent.Value = rstVal.Fields(j).Value
    Set rngCurrent = rngCurrent.Offset(0, 1)
    Next
    rstVal.MoveNext
    Next
    .
    .

    Thanks a lot in advance,
    Heiko

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I use something loosely based off of pootle flump's code and it seems to work pretty well for me. I've transferred around 10k rows × 30 rows (including a memo field) in a few minutes.
    Me.Geek = True

Posting Permissions

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