arnab0711
02-20-2011, 07:16 AM
Hi,
I have this code which works on the sheet input although the code marked in red is not working,pl advice
Sub test()
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, sqlstr As String
With ActiveWorkbook.Sheets(3): If .[a2] <> "" Then .Range(.[a2], .[a2].End(xlDown)).Resize(, 56).Clear
End With: With ActiveWorkbook: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & .Path & "\" & .Name & ";Extended Properties=""Excel 8.0;HDR=No;"";"
sqlstr = "SELECT T1.F1,T1.F2,T1.F3,T1.F4,T1.F5,T1.F6,T1.F7,T1.F8,T1.F9,T1.F10,T1.F11,T1.F12, T1.F13,T1.F14,T1.F15,T1.F16,T1.F17," & _
"T1.F18,T1.F19,T1.F20,T1.F21,T1.F22,T1.F23,T1.F24,T1.F25,T1.F26,T1.F27,T1.F2 8,T1.F29,T1.F30,T1.F31,T1.F32,T1.F33,T1.F34,T1.F35," & _
"T1.F36,T1.F37,T1.F38,T1.F39,T1.F40,T1.F41,T1.F42,T1.F43,T1.F44,T1.F45,T1.F4 6,T1.F47,T1.F48,T1.F49,T1.F50,T1.F51,T1.F52,T1.F53," & _
"T1.F54,T1.F55,T1.F56 FROM `raw data$A2:BD65536` T1 WHERE"
With Sheets("Input")
Select Case .[e3]
Case Is <> ""
sqlstr = sqlstr & " UCASE(T1.F4) LIKE '%" & UCase(.[e3]) & "%'"
If .[f3] <> "" Then sqlstr = sqlstr & " AND UCASE(T1.F8) LIKE '%" & UCase(.[f3]) & "%'"
Case Is = ""
sqlstr = sqlstr & " UCASE(T1.F8) LIKE '%" & UCase(.[f3]) & "%'"
If .[g3] <> "" Then sqlstr = sqlstr & " AND UCASE(T1.F8) LIKE '%" & UCase(.[g3]) & "%'"
Case Is = ""
sqlstr = sqlstr & " UCASE(T1.F8) LIKE '%" & UCase(.[g3]) & "%'"
End Select
rs.Open sqlstr, cn
With Sheets(3): .[a2].CopyFromRecordset rs: [a:bd].EntireColumn.AutoFit: End With
End With: Set rs = Nothing: Set cn = Nothing: End With: End Sub
I have this code which works on the sheet input although the code marked in red is not working,pl advice
Sub test()
Dim cn As New ADODB.Connection, rs As New ADODB.Recordset, sqlstr As String
With ActiveWorkbook.Sheets(3): If .[a2] <> "" Then .Range(.[a2], .[a2].End(xlDown)).Resize(, 56).Clear
End With: With ActiveWorkbook: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & .Path & "\" & .Name & ";Extended Properties=""Excel 8.0;HDR=No;"";"
sqlstr = "SELECT T1.F1,T1.F2,T1.F3,T1.F4,T1.F5,T1.F6,T1.F7,T1.F8,T1.F9,T1.F10,T1.F11,T1.F12, T1.F13,T1.F14,T1.F15,T1.F16,T1.F17," & _
"T1.F18,T1.F19,T1.F20,T1.F21,T1.F22,T1.F23,T1.F24,T1.F25,T1.F26,T1.F27,T1.F2 8,T1.F29,T1.F30,T1.F31,T1.F32,T1.F33,T1.F34,T1.F35," & _
"T1.F36,T1.F37,T1.F38,T1.F39,T1.F40,T1.F41,T1.F42,T1.F43,T1.F44,T1.F45,T1.F4 6,T1.F47,T1.F48,T1.F49,T1.F50,T1.F51,T1.F52,T1.F53," & _
"T1.F54,T1.F55,T1.F56 FROM `raw data$A2:BD65536` T1 WHERE"
With Sheets("Input")
Select Case .[e3]
Case Is <> ""
sqlstr = sqlstr & " UCASE(T1.F4) LIKE '%" & UCase(.[e3]) & "%'"
If .[f3] <> "" Then sqlstr = sqlstr & " AND UCASE(T1.F8) LIKE '%" & UCase(.[f3]) & "%'"
Case Is = ""
sqlstr = sqlstr & " UCASE(T1.F8) LIKE '%" & UCase(.[f3]) & "%'"
If .[g3] <> "" Then sqlstr = sqlstr & " AND UCASE(T1.F8) LIKE '%" & UCase(.[g3]) & "%'"
Case Is = ""
sqlstr = sqlstr & " UCASE(T1.F8) LIKE '%" & UCase(.[g3]) & "%'"
End Select
rs.Open sqlstr, cn
With Sheets(3): .[a2].CopyFromRecordset rs: [a:bd].EntireColumn.AutoFit: End With
End With: Set rs = Nothing: Set cn = Nothing: End With: End Sub