VBA & SQL how to select specific values based on range in excel?

Question

I am newbie in connection of vba (excel) and oracle database. I have tried to look for some information but I could not find anything that would work for me.

I want to write a query that will return me only rows in which there is a specific values. My query looks like this: SQLStr = SQLStr = "SELECT NGKHFHCD, NGKHFNAM, NGKHGNKA, NGKHSZIC, NGKHMTRC, NGKHSNZC, NGKHGCHC, NGKHKKKS, NGKHKTKS FROM NGKH　order by NGKHFHCD"

But I want to have something that will be like this SQLStr = "SELECT NGKHFHCD, NGKHFNAM, NGKHGNKA, NGKHSZIC, NGKHMTRC, NGKHSNZC, NGKHGCHC, NGKHKKKS, NGKHKTKS FROM NGKH　WHERE NGKHFHCD = SHeet1(A2:A)"

I just don't want to pull out whole table from oracle, because it will take a lots of time so I thought that maybe I can return only specific rows from that table. Also if there is no searched value in the table I would like to mark it in someway.

Is there anyway to solve it?

my code:

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

You want to query on a single value? Is the value numeric or text? — Tim Williams, Apr 12 '21 at 04:31
it is a text and it will be multiple values. (the whole range for example A2:A1000 so it would be 999 different values) — Timonek, Apr 12 '21 at 04:39
With Oracle that's a bit of an issue - see for example: https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause — Tim Williams, Apr 12 '21 at 04:47

Tim Williams · Accepted Answer · 2021-04-12T05:48:24.680

Untested but this would be close:

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

it works. I was able to pull out 10k rows from oracle thanks to that code. Thanks a lot! — Timonek, Apr 12 '21 at 06:46

VBA & SQL how to select specific values based on range in excel?

1 Answers1