时间戳

首页 > 解决方案 > VBA 和 Oracle 数据库通用连接

excel - VBA 和 Oracle 数据库通用连接

问题描述

我正在尝试使用 vba 连接到 oracle 数据库，但无法使其正常工作。该代码与某种表格完美配合，但有些表格会给我一个错误13 type mismatch，有时3021 bof or eof is true

不匹配开启：mtxData = Application.Transpose(rs.GetRows)

我不知道为什么会这样。另外我想说这是我第一次使用 oracle 和 vba。

到目前为止我的代码：

Sub Load_data()
        Dim cn As ADODB.Connection
        Set cn = New ADODB.Connection

     cn.Open ( _
    "Provider=*****; User ID = ****; Password = ****; Data Source = *****")

        Dim i As Long
        For i = 1 To 20
            Load_data_into_sheet Sheets("Sheet1"), "COLLABNAME" & i, cn
        Next

        cn.Close

    End Sub

   Private Sub Load_data_into_sheet(ws As Worksheet, CollabName As String, cn As ADODB.Connection)
        ws.Select
        Dim rs As ADODB.recordSet
        Dim col As Integer
        Dim row As Integer
        Dim Query As String
        Dim mtxData As Variant


        Set rs = New ADODB.recordSet

    rs.Open "select * from ZAR01", cn
    With ws
            col = 0
             'First Row: names of columns
            Do While col < rs.Fields.Count
                .Cells(1, col + 1) = rs.Fields(col).Name
                col = col + 1
            Loop


            mtxData = Application.Transpose(rs.GetRows)
            .Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData




        End With
        rs.Close

'adOpenStatic, adLockReadOnly, adCmdText Or adAsyncExecute
    End Sub

标签： excelvbaoracle

解决方案

我找到了解决方案。希望有人会发现它有用

Sub OracleLocalConnect()
  Dim RecordSet As New ADODB.RecordSet
  Dim con As New ADODB.Connection
  Dim ExcelRange As Range
  Dim SQLStr As String
  
  Dim ws As Worksheet

  con.ConnectionString = "Provider=****; User ID = ****; Password = ****; Data Source = ****"
  
  con.Open
  Set RecordSet = CreateObject("ADODB.Recordset")

  SQLStr = "SELECT * FROM WIN1 WHERE (nvl(win1cmfg,' ') <> 'C' and nvl(win1scfg,' ') <> 'C' and win1rcls = '00' and win1rsts = '0' )"
  

  RecordSet.Open SQLStr, con, adOpenStatic, adLockReadOnly
  
  Set ws = ActiveWorkbook.Sheets("Sheet1")
  Set ExcelRange = ws.Range("A2")
  ExcelRange.CopyFromRecordset RecordSet
  
  RecordSet.Close
  con.Close
Exit Sub

End Sub

推荐阅读