时间戳

首页 > 解决方案 > VBA和SQL如何根据excel中的范围选择特定值？

sql - VBA和SQL如何根据excel中的范围选择特定值？

问题描述

我是连接vba（excel）和oracle数据库的新手。我试图寻找一些信息，但我找不到任何对我有用的东西。

我想编写一个查询，它只返回包含特定值的行。我的查询如下所示： SQLStr = SQLStr = "SELECT NGKHFHCD, NGKHFNAM, NGKHGNKA, NGKHSZIC, NGKHMTRC, NGKHSNZC, NGKHGCHC, NGKHKKKS, NGKHKTKS FROM NGKH　order by NGKHFHCD"

但我想拥有这样的东西SQLStr = "SELECT NGKHFHCD, NGKHFNAM, NGKHGNKA, NGKHSZIC, NGKHMTRC, NGKHSNZC, NGKHGCHC, NGKHKKKS, NGKHKTKS FROM NGKH　WHERE NGKHFHCD = SHeet1(A2:A)"

我只是不想从 oracle 中取出整个表，因为这会花费很多时间，所以我想也许我只能从该表中返回特定的行。此外，如果表中没有搜索到的值，我想以某种方式标记它。

有什么办法解决吗？

我的代码：

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=OraOLEDB.Oracle.1;User ID=***;Password=****;Data Source=*****;"
  
  con.Open
  Set RecordSet = CreateObject("ADODB.Recordset")

  SQLStr = "SELECT GNKHFHCD, GNKHFNAM, GNKHGNKA, GNKHSZIC, GNKHMTRC,  GNKHSNZC, GNKHGCHC, GNKHKKKS, GNKHKTKS  FROM GNKH ORDER BY GNKHFHCD"  


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

End Sub

标签： sqlexcelvba

解决方案

未经测试，但这将是接近的：

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=OraOLEDB.Oracle.1;User ID=***;Password=****;Data Source=*****;"
    
    con.Open
    Set RecordSet = CreateObject("ADODB.Recordset")
    
    SQLStr = " SELECT GNKHFHCD, GNKHFNAM, GNKHGNKA, GNKHSZIC, GNKHMTRC, " & _
            " GNKHSNZC, GNKHGCHC, GNKHKKKS, GNKHKTKS  FROM GNKH " & _
            " where " & InClause(Sheet1.Range("A2:A1000"), "GNKHFHCD", True) & _
            " ORDER BY GNKHFHCD "
    
    RecordSet.Open SQLStr, con, adOpenStatic, adLockReadOnly
    
    Set ws = ActiveWorkbook.Sheets("Prices")
    Set ExcelRange = ws.Range("A2")
    ExcelRange.CopyFromRecordset RecordSet
    
    RecordSet.Close
    con.Close

End Sub

'Create an in clause for an Oracle query
Function InClause(rng As Range, colName As String, Optional quoted As Boolean = False)
'https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause
    Dim s As String, c As Range, qt As String, sep As String
    qt = IIf(quoted, "'", "")
    sep = ""
    s = "(999, " & colName & ") in ("
    For Each c In rng.Cells
        If Len(c.Value) > 0 Then
            s = s & sep & vbLf & "(999," & qt & c.Value & qt & ")"
            sep = "," 'add comma after first pass
        End If
    Next c
    InClause = s & ")"
End Function

推荐阅读