Atravis
03-07-2011, 10:44 AM
Hi everyone,
I have created two recordsets from a workbook. The recordsets contain the records from the used range from a sheet s5 and a sheet s10 providing the criteria is met. The code below creates the first two recordsets fine, however I am trying to Join these
two recordsets to create a third where s5.column(A) = s10.column(B) values. I am getting errors stating the objRecordset cannot be found. Any ideas?
Thanks
'set record set variables
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
'ser connection and recordset
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
Set objRecordset2 = CreateObject("ADODB.Recordset")
Set objRecordset3 = CreateObject("ADODB.Recordset")
Set objConnection = GetExcelConnection(ThisWorkbook.FullName)
objRecordset.Open "SELECT * FROM [" & s5.Name & "$" & s5.UsedRange.Address(False, False) & "] WHERE F8 LIKE '" & s1.Cells(6, "B") & "%'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
objRecordset2.Open "SELECT * FROM [" & s10.Name & "$" & s10.UsedRange.Address(False, False) & "] WHERE F1 LIKE '" & s1.Cells(6, "B") & "%'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
objRecordset3.Open "SELECT * FROM objRecordset RIGHT JOIN objRecordset2 ON objRecordset.F1=objRecordset2.F2", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
s11.Cells(1, "A").CopyFromRecordset objRecordset3
This gives the connection i am using.
'set up a connection (used with xls), set not to use headers
Public Function GetExcelConnection(ByVal Path As String, _
Optional ByVal Headers As Boolean = False) As Connection
Dim strConn As String
Dim objConn As ADODB.Connection
'set connection (use excel 8, mixed data as text and headers set off as standard)
Set objConn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=""Excel 8.0;IMEX=1;HDR=" & _
IIf(Headers, "Yes", "No") & """"
'open connection
objConn.Open strConn
'give back connection
Set GetExcelConnection = objConn
End Function
I have created two recordsets from a workbook. The recordsets contain the records from the used range from a sheet s5 and a sheet s10 providing the criteria is met. The code below creates the first two recordsets fine, however I am trying to Join these
two recordsets to create a third where s5.column(A) = s10.column(B) values. I am getting errors stating the objRecordset cannot be found. Any ideas?
Thanks
'set record set variables
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
'ser connection and recordset
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
Set objRecordset2 = CreateObject("ADODB.Recordset")
Set objRecordset3 = CreateObject("ADODB.Recordset")
Set objConnection = GetExcelConnection(ThisWorkbook.FullName)
objRecordset.Open "SELECT * FROM [" & s5.Name & "$" & s5.UsedRange.Address(False, False) & "] WHERE F8 LIKE '" & s1.Cells(6, "B") & "%'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
objRecordset2.Open "SELECT * FROM [" & s10.Name & "$" & s10.UsedRange.Address(False, False) & "] WHERE F1 LIKE '" & s1.Cells(6, "B") & "%'", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
objRecordset3.Open "SELECT * FROM objRecordset RIGHT JOIN objRecordset2 ON objRecordset.F1=objRecordset2.F2", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
s11.Cells(1, "A").CopyFromRecordset objRecordset3
This gives the connection i am using.
'set up a connection (used with xls), set not to use headers
Public Function GetExcelConnection(ByVal Path As String, _
Optional ByVal Headers As Boolean = False) As Connection
Dim strConn As String
Dim objConn As ADODB.Connection
'set connection (use excel 8, mixed data as text and headers set off as standard)
Set objConn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=""Excel 8.0;IMEX=1;HDR=" & _
IIf(Headers, "Yes", "No") & """"
'open connection
objConn.Open strConn
'give back connection
Set GetExcelConnection = objConn
End Function