Hello everyone, 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: 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.
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
Your problem is the type of the variable i. It should be of the Long type. It doesn't make sense to use the Integer type at this time. It is implicitly converted to Long when the macro runs. Artik
