Alter SQL script in Excel

[Malcolm]

I have a database connection in excel (with sql server script) and would like the where clause to be affected by what is in column A of the excel sheet
e.g
'where x in(sheet 1, column A:A)'

is this possible without actually altering the script directly/

 

[Deepak]

Hi,

Your post doesn't throw a clue to me what u are asking for!!

Meanwhile you might in need of something like as below.

'Defines the path of the database
source1 = "C:\CAF_DB\AIO_DB.2015v1.xlsb" 'ThisWorkbook.Path & "\" & "Database.xlsx"
source2 = ThisWorkbook.FullName ' & "\" & "Query Upload File.xlsx"

'Making a connection string
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & source1 & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"
cn.Open strCon
strQuery = "SELECT [S#No], [MSISDN], [AVAM Date], [Remarks/Rejection/Failed Reason], [FTD AVAM Status]," & _
"[Upload_File_Name], [TV], [Mobile_Number], [UPC], [Previous_Service_Provider], [Existing_Number]," & _
"[Customer_Name], [Father_Husband_Name], [Local_Address], [POI_Type], [POI_Document_No] FROM [Cust_DB$A:AD] WHERE " & _
"MSISDN IN(SELECT Bulk_MSISDNS FROM [Excel 12.0;DATABASE=" & source2 & ";HDR=YES;].[Bulk_Query$A:A]);"
            Set rst = New ADODB.Recordset
                rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
                    .Cells(2, 2).CopyFromRecordset rst
                    lastrow = rst.RecordCount
                rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing
Set s = Nothing