D
Dan
Hello all -
I have a macro which calls out to a SQL database table and populates
into an Excel sheet. There are 1700 records in the SQL table, but it
is taking close to 2 minutes to populate the records back into Excel.
The slowdown seems to be in the loop that was created.
Any ideas on how to this macro can process faster?
Private Sub Workbook_Open()
Dim cnn1 As ADODB.Connection
Dim runspcmd As ADODB.Command
Dim x As Integer
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "Provider=sqloledb;Data
Source=000.00.000.000;Initial Catalog=brdatadb;User
Id=stuff;Password=stuff;"
cnn1.ConnectionTimeout = 300
cnn1.Open
Set runspcmd = New ADODB.Command
runspcmd.ActiveConnection = cnn1
runspcmd.CommandTimeout = 120
x = 0
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
runspcmd.CommandText = "select vendor_code, vendor_name from vendor
order by vendor_name "
rs.Open runspcmd
For x = 1 To rs.RecordCount
If Not rs.EOF Then
Me.Worksheets.Item(2).Cells(x + 1, 1).Value = rs("vendor_name")
Me.Worksheets.Item(2).Cells(x + 1, 2).Value = rs("vendor_code")
End If
rs.MoveNext
Next
rs.Close
cnn1.Close
End Sub
Thanks for any assistance!
Dan
I have a macro which calls out to a SQL database table and populates
into an Excel sheet. There are 1700 records in the SQL table, but it
is taking close to 2 minutes to populate the records back into Excel.
The slowdown seems to be in the loop that was created.
Any ideas on how to this macro can process faster?
Private Sub Workbook_Open()
Dim cnn1 As ADODB.Connection
Dim runspcmd As ADODB.Command
Dim x As Integer
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "Provider=sqloledb;Data
Source=000.00.000.000;Initial Catalog=brdatadb;User
Id=stuff;Password=stuff;"
cnn1.ConnectionTimeout = 300
cnn1.Open
Set runspcmd = New ADODB.Command
runspcmd.ActiveConnection = cnn1
runspcmd.CommandTimeout = 120
x = 0
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
runspcmd.CommandText = "select vendor_code, vendor_name from vendor
order by vendor_name "
rs.Open runspcmd
For x = 1 To rs.RecordCount
If Not rs.EOF Then
Me.Worksheets.Item(2).Cells(x + 1, 1).Value = rs("vendor_name")
Me.Worksheets.Item(2).Cells(x + 1, 2).Value = rs("vendor_code")
End If
rs.MoveNext
Next
rs.Close
cnn1.Close
End Sub
Thanks for any assistance!
Dan