在excel中迭代数据库查询结果在vba中

Question

我想打开一个到SQL数据库的连接，然后访问单个单元。我有一个使用PivotTableWizard的示例（如下所示）。我想知道一种没有任何数据透视表的方法 - 我想逐个单元地进行迭代。或者这个数据透视表向导也适用于这个目的吗？

提到的例子：

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 /卡罗尔

Answer 1

如果你想迭代通过细胞的细胞，你可以做这样的事情：

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 

上面的代码迭代通过前100行和前25列，并在Visual Basic编辑器的调试窗口中打印单元格地址及其值。