How to quickly insert 300K records into Excel file via VBS?

Question

Oracle

So records incredibly long

' выводим содержимое таблиц и считаем записи
            recordsCount = 0
            RSSQL.CursorLocation = 3 ' клиентский курсор
            RSSQL.OPEN strSQL
            iFieldsCount = RSSQL.Fields.Count
              While Not RSSQL.EOF
                For J = 1 To iFieldsCount Step 1
                  obj_WorkSheet.Cells(RSSQL.AbsolutePosition+3, j) = RSSQL(j-1)
                Next
              RSSQL.movenext
              Wend
            RSSQL.Close

So it fails with an error that there is not enough memory:

recordsCount = 0
            RSSQL.CursorLocation = 3 ' клиентский курсор
            RSSQL.OPEN strSQL
              obj_WorkSheet.Range("A4").CopyFromRecordset RSSQL
              recordsCount = RSSQL.RecordCount
            RSSQL.Close

How to take records from the database painlessly and quickly?
It is possible in the 2nd variant to change the cursor to the server one, but then I will not be able to get the number of records.

Answer 1

Import from db to csv

Answer 2

Excel natively knows how to take data from the database.

Answer 3

check what slows down your code. run in sections and measure the speed of execution

Class module for checking the speed of execution of a section of code

'---------------------------------------------------------------------------------------
' Module    : CTimeCounter
' DateTime  : 25.12.2017 00:25
' Author    : Mike Woodhouse
' Purpose   : Класс таймера для подсчета времени работы кода
'
'            Sub test()
'               Dim tc As New CTimeCounter
'               tc.StartCounter
'                 Тестируемый код
'               Debug.Print tc.TimeElapsed
'            End Sub
'
'---------------------------------------------------------------------------------------
Option Explicit

Private Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
End Type

#If VBA7 Then
   Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias "QueryPerformanceCounter" (cyTickCount As LARGE_INTEGER) As Long
   Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As LARGE_INTEGER) As Long
#Else
   Private Declare Function getTickCount Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
   Private Declare Function getFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long
#End If



Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_crFrequency As Double

Private Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#
'

Private Function LI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
    Low = LI.lowpart
    If Low < 0 Then
        Low = Low + TWO_32
    End If
    LI2Double = LI.highpart * TWO_32 + Low
End Function

Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
    getFrequency PerfFrequency
    m_crFrequency = LI2Double(PerfFrequency)
End Sub

Public Sub StartCounter()
    getTickCount m_CounterStart
End Sub

Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
    getTickCount m_CounterEnd
    crStart = LI2Double(m_CounterStart)
    crStop = LI2Double(m_CounterEnd)
    TimeElapsed = (1000# * (crStop - crStart)) / m_crFrequency
End Property

2.

obj_WorkSheet.Cells()

Such a call in a cell, even without access to the database, in the presence of 300k calls, will already slow down your Excel. Collect everything in a 2-dimensional array and assign in one assignment
. Perhaps it would be appropriate to get the entire block of data and process it already in Excel
. Or maybe split one large query into several?

Answer 4

You (almost always) don't need to take care of object deletion in python.