iterate over database query results in vba in excel

Question

Hi. I would like to open a connection to SQL database, and then have access to individual cells. I have an example that uses PivotTableWizard (presented below). I would like to know of a way that does not have anything to do Pivot Tables - I would like to iterate cell-by-cell. Or is this PivotTableWizard suitable for that purpose also?

The example mentioned:

ConnectionString = "Driver={SQL Server};Server=Serversql11;Persist Security Info=False;Integrated Security=SSPI;Database=DB_IC;"

PivotName = "Talks"

QArray = Array(ConnectionString, _
"exec dbo.talksReport '" & CStr(param_date) & "'")

Worksheets("Talks").PivotTableWizard xlExternal, QArray, Worksheets("Talks").Range("A1"), PivotName

TIA /Karol

Answer 1

If you want to iterate cell by cell, you could do something like this:

Sub PrintCellContentsToDebugWindow()
  Dim ws As Worksheet
  Dim rng As Range
  Dim intCounterRow As Integer
  Dim intCounterCol As Integer
  Dim intMaxRow As Integer
  Dim intMaxCol As Integer

  Set ws = ActiveSheet   'OR   '
  'Set ws = ActiveWorkbook.Sheets("Sheet1")   '

  intCounterRow = 1
  intCounterCol = 1
  intMaxRow = 100
  intMaxCol = 25

  Do While intCounterCol <= intMaxCol
    intCounterRow = 1
    Do While intCounterRow <= intMaxRow
      Set rng = ws.Cells(intCounterRow, intCounterCol)
      Debug.Print rng.Address & "    " & rng.Value

      intCounterRow = intCounterRow + 1
    Loop
    intCounterCol = intCounterCol + 1
  Loop

End Sub

The above code iterates through the first 100 rows and the first 25 columns and prints out the cell address and its value in the Debug Window in the Visual Basic Editor.