I am trying to figure out a quick, automatized way to insert some real-time date from a financial service provider into excel, which can only be fetched with an excel-plugin in blocks of roughly 100,000 values. At the moment I am using the code below and so far it seems to be working as expected:
Sub insert()
'Declare Variables
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim ColumnsSchema As ADODB.Recordset
Dim rsT As Variant
Dim i As Integer
For i = 0 To 2
rsT = Join(Application.Transpose(ThisWorkbook.Sheets("Prices").Range(Cells(3 + i * 10000, 9), Cells(10002 + i * 10000, 9)).Value), " ")
' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=DB1;" 'rest of the string blackened
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
'Open the connection and execute.
conn.Open sConnString
Set rs = conn.Execute(rsT)
Next i
' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub
My current problem is as soon as I increase the counter i to 3, thus inserting more than 30000 with the for-loop, it results in an overflow-error. I already tried to split it up in smaller blocks and a counter going to 49: Similar error.
The referenced cells in the sheet ‘prices’ seem all correct. That’s why I am only posting the VBA-code here. As I am not really familiar with VBA and the limitations of the used objects, I would expect the issue there and maybe someone with more VBA-experience here can see it at a glance.
Any advice is highly appreciated and thank you very much for your time to read this post. Hope I could at least make my case clear enough.
