Excel VBA - Run SQL Query using Workfsheet Cell Range

Question

I am creating a simple spreadsheet which takes an array of IDs from worksheet "input", queries an Oracle database asking for only the records which match the IDs in the array and outputs the results to worksheet "output".

So far, my VBA will work if my array only contains a single ID (by specifying a single cell range), and everything completes with the desired output from the Oracle database appearing in worksheet "output". Good times.

The problem I am having now is that I want to specify a range of IDs (anything up to around 5000) in worksheet "input" to include in my array and pass that array to the Oracle database to return data for each ID it finds (I am not expecting all IDs to exist). Whenever I try this I seem to get "Error 13 Type Mismatch" errors... Bad times.

My VBA code is:

Dim OracleConnection As ADODB.Connection
Dim MosaicRecordSet As ADODB.RecordSet
Dim SQLQuery As String
Dim DBConnect As String
Dim count As String
Dim strbody As String
Dim Exclude As String
Dim i As Integer
Dim Rec As RecordSet
Dim InputIDs As Variant

Set OracleConnection = New ADODB.Connection
DBConnect = "Provider=msdaora;Data Source=MOSREP;User ID=***;Password=***;"
OracleConnection.Open DBConnect

' Clear Output Sheet Down
Sheets("Output").Select
Range("A2:F10000").Clear

' Set Input Range
Sheets("Input").Columns("A:A").NumberFormat = "0"
InputIDs = Sheets("Input").Range("A2:A10").Value

' SQL Query
SQLQuery = "select DMP.PERSON_ID, DMP.FULL_NAME, DMP.DATE_OF_BIRTH, DMA.ADDRESS, DMA.ADDRESS_TYPE, DMA.IS_DISPLAY_ADDRESS " & _
"from DM_PERSONS DMP " & _
"join DM_ADDRESSES DMA " & _
"on DMA.PERSON_ID=DMP.PERSON_ID " & _
"where DMP.PERSON_ID in (" & InputIDs & ")"

Set MosaicRecordSet = OracleConnection.Execute(SQLQuery)

Sheets("Output").Range("A2").CopyFromRecordset MosaicRecordSet

' Change DOB Format
Sheets("Output").Columns("C:C").NumberFormat = "dd/mm/yyyy"
' Set Left Alignment
Sheets("Output").Columns("A:Z").HorizontalAlignment = xlHAlignLeft

Range("A1").Select

OracleConnection.Close
Set MosaicRecordSet = Nothing
Set OracleConnection = Nothing

ActiveWorkbook.Save

Can anyone shed light on what I am missing? I have attempted to resolve the Type Mismatch issue by setting the 'numberformat' on the column in worksheet "input" to "0" but that didn't help. I also thought that I might have to have a loop to iterate through each record, but I haven't got to that stage yet because of this Type Mismatch thing...

Thank you everyone for your help in advance!

Regards Matt

You can't pass a variant array as an argument for a query. You would need it to be a string with the ids separated by commas. — Kyle
Thank you Kyle, your response along with Thomas' below has helped me resolve my issue. Until you explained I didn't realise that variant arrays don't include comma delimiters. — Matt Prime

Unknown Unknown · Accepted Answer · 2016-07-06T13:48:05

The ID's need to be comma delimited

InputIDs = getIDs( Sheets("Input").Range("A2:A10") )

Function getIDs(rng As Range)
    Dim c As Range
    Dim s As String
    For Each c In rng
        s = s & c.Value & ","
    Next
    getIDs = Left(s, Len(s) - 1)
End Function

Excel VBA - Run SQL Query using Workfsheet Cell Range

1 Answers