none
Access VBA: a more effective way for Excel data insertion? RRS feed

  • Question

  • Hi community,

    I wrote the following VBA in MS Access, it is working as expected. But the insertion speed is slow. Do you know a better to write the VBA code? I need some good insertion speed.

    Set rstSet1 = CurrentDb.OpenRecordset("Select Col1, Col2, Col3, Col4, Col5, " & _
                                                 "Col6, Col7, Col8, Col9, Col10 " & _
                                          "FROM " & rstSet2.Fields("TabName").Value)                        
        lngCount = 9
                                 
        Do Until rstSet1.EOF
    		rptTab = rstSet2.Fields("TabName").Value
                         
            wb1.Sheets(rptTab).Activate
            wb1.Sheets(rptTab).Range("D" & lngCount).Value = rstSet1!Col1
            wb1.Sheets(rptTab).Range("E" & lngCount).Value = rstSet1!Col2
            wb1.Sheets(rptTab).Range("F" & lngCount).Value = rstSet1!Col3
            wb1.Sheets(rptTab).Range("G" & lngCount).Value = rstSet1!Col4
            wb1.Sheets(rptTab).Range("H" & lngCount).Value = rstSet1!Col5
            wb1.Sheets(rptTab).Range("I" & lngCount).Value = rstSet1!Col6
            wb1.Sheets(rptTab).Range("J" & lngCount).Value = rstSet1!Col7
            wb1.Sheets(rptTab).Range("K" & lngCount).Value = rstSet1!Col8
            wb1.Sheets(rptTab).Range("L" & lngCount).Value = rstSet1!Col9
            wb1.Sheets(rptTab).Range("M" & lngCount).Value = rstSet1!Col10
            rstSet1.MoveNext
            lngCount = lngCount + 1
         Loop


    • Edited by MelZZ Thursday, September 17, 2015 6:03 PM typo
    Thursday, September 17, 2015 6:01 PM

Answers

All replies