I'm trying to query data from my two sheets in the workbook and save the result on the third sheet. But I keep getting the following error:
Run-time error '-2147467259 (80004005)':
The connection for viewing your linked Microsoft Excel worksheet was lost.
The odd thing is, I use this code for almost all of my macros and I've NEVER had any issues. And for some reason it even ran once before! I don't know what's causing it. Could anybody help?
Dim objCon1 As ADODB.Connection, dataSQL As String, dataRS1 As ADODB.Recordset
Set wb = ThisWorkbook
Set P_ws = wb.Worksheets("SECDATA")
Set PL_ws = wb.Worksheets("PLDATA")
Set output_ws = wb.Worksheets("Output")
Set objCon1 = New ADODB.Connection
objCon1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
dataSQL = "SELECT distinct p.COPYID as ID, d.* " & _
"FROM [PLDATA$] as p, [SECDATA$] as d " & _
"WHERE p.[CCodes] = d.TICKER "
Set dataRS1 = New ADODB.Recordset
dataRS1.Open dataSQL, objCon1
y = 1
For Each fld In dataRS1.Fields
output_ws.Cells(1, y).Value = fld.Name
y = y + 1
output_ws.Cells(2, 1).CopyFromRecordset dataRS1
Set objCon1 = Nothing
Set dataRS1 = Nothing