使用 Excel VBA 从 Oracle 表中检索数据到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14107870/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Retrieving data from Oracle table to Excel using Excel VBA
提问by tester
I have written code to get data from a table in Oracle DB and dump to an Excel sheet using VBA.
我编写了代码来从 Oracle DB 中的表中获取数据并使用 VBA 转储到 Excel 工作表。
In Excel, it displays the first row repeatedly. For an instance, if there are 45 different rows returned from the DB, in the Excel sheet all 45 rows are the same as the first row in the DB.
在 Excel 中，它重复显示第一行。例如，如果从 DB 返回了 45 个不同的行，则在 Excel 工作表中，所有 45 行都与 DB 中的第一行相同。
How to get the rows from the DB to Excel?
如何将行从数据库获取到 Excel？
Sub Results()
Dim SQL As String
Dim OraDynaSet As Object
Dim i As Integer
SQL = "Select * from Employee where EmpID=20"
Set OraDynaSet = objDataBase.DBCreateDynaset(SQL, 0&)
If OraDynaSet.RecordCount > 0 Then
'There were records retrieved
OraDynaSet.MoveFirst
For ICOLS = 0 To OraDynaSet.Fields.Count - 1
.Cells(1, ICOLS + 1).Value = OraDynaSet.Fields(ICOLS).Name
Next ICOLS
'Loop the recordset for returned rows
For i = 0 To OraDynaSet.RecordCount - 1
For j = 0 To ICOLS - 1
.Cells(2 + i, j + 1) = OraDynaSet.Fields(j).Value
Next j
Next i
Else
MsgBox "No Matching records found"
End If
End Sub
回答by IAmAnonymous
Dim SQL As String
Dim OraDynaSet As Object
Dim i As Integer
SQL = "Select * from Employee where EmpID=20"
Set OraDynaSet = objDataBase.DBCreateDynaset(SQL, 0&)
If OraDynaSet.RecordCount > 0 Then
'There were records retrieved
OraDynaSet.MoveFirst
For ICOLS = 0 To OraDynaSet.Fields.Count - 1
.Cells(1, ICOLS + 1).Value = OraDynaSet.Fields(ICOLS).Name
Next ICOLS
'Loop the recordset for returned rows
For i = 0 To OraDynaSet.RecordCount - 1
For j = 0 To ICOLS - 1
.Cells(2 + i, j + 1) = OraDynaSet.Fields(j).Value
Next j
OraDynaSet.Movenext
Next i
Else
MsgBox "No Matching records found"
End If
End Sub
