VBA Basic error help

[Mike2502]

Hey all,

I've got a VBA code but I'm trying to configure it to my needs but every statement I try I fail.

I need to set an IF/WHERE ID number equals a row on my spreadsheet... I need to copy the whole row if the ID number is matched

Thanks in advance folks

'Add reference for Microsoft Activex Data Objects Library

Sub sbADO()
Dim sSQLQry As String
Dim ReturnArray

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

Dim DBPath As String, sconnect As String



DBPath = ThisWorkbook.FullName

'You can provide the full path of your external file as shown below
'DBPath ="C:\InputData.xlsx"

sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

Conn.Open sconnect
[B]    sSQLSting = "SELECT * From [DataSheet$] WHERE (ID = '1')" '[/B]
    
    mrs.Open sSQLSting, Conn
        '=>Load the Data into an array
        'ReturnArray = mrs.GetRows
                ''OR''
        '=>Paste the data into a sheet
        ActiveSheet.Range("A2").CopyFromRecordset mrs
    'Close Recordset
    mrs.Close

'Close Connection
Conn.Close

End Sub

 

[6StringJazzer]

What error do you get when you run this code?

I am not familiar with using an ADODB connection with SQL to access an Excel file from Excel VBA, but there are two things I would check.

First, I do not know how the string "[DataSheet$]" is intended. If it is supposed to be a VBA variable, it is not declared or set, and will not be interpreted as a variable since it is right in the SQL string. Maybe I just don't understand how it's being used.

Second, the SQL searches for the string '1' but the data is probably numeric. I do not know if the necessary conversion will take place. I would remove the single quotes and see if that works.

This method seems rather complicated for looking for a value. Are you trying to avoid opening the file at DBPath?

 

[Mike2502]

What error do you get when you run this code?

I am not familiar with using an ADODB connection with SQL to access an Excel file from Excel VBA, but there are two things I would check.

First, I do not know how the string "[DataSheet$]" is intended. If it is supposed to be a VBA variable, it is not declared or set, and will not be interpreted as a variable since it is right in the SQL string. Maybe I just don't understand how it's being used.

Second, the SQL searches for the string '1' but the data is probably numeric. I do not know if the necessary conversion will take place. I would remove the single quotes and see if that works.

This method seems rather complicated for looking for a value. Are you trying to avoid opening the file at DBPath?

Hi mate

I'm a newbie so can't really action anything of the above you've said but I will look into it so cheers.

How would one make a string and the copy row on multiple conditions if met?

 sSQLSting = "SELECT * From [DataSheet$] WHERE (ID = 2, 3)"