使用Excel VBA将数据从Oracle表检索到Excel [英] Retrieving data from Oracle table to Excel using Excel VBA

问题描述

我已经编写了代码来从Oracle DB中的表中获取数据，并使用VBA转储到Excel工作表中.

I have written code to get data from a table in Oracle DB and dump to an Excel sheet using VBA.

在Excel中，它重复显示第一行.例如，如果从数据库返回了45条不同的行，则在Excel工作表中，所有45行都与数据库中的第一行相同.

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?

How to get the rows from the DB to 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

推荐答案

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

这篇关于使用Excel VBA将数据从Oracle表检索到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！