Solved

Jet OLEDB error! Viewing your linked MS Excel worksheeet was lost

Posted on 2013-12-03
4
1,237 Views
Last Modified: 2013-12-04
Hi,

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")

output_ws.Cells.ClearContents

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
    Next
    output_ws.Cells(2, 1).CopyFromRecordset dataRS1

    objCon1.Close
    Set objCon1 = Nothing
    Set dataRS1 = Nothing

Open in new window

0
Comment
Question by:iamnamja
  • 2
4 Comments
 
LVL 4

Assisted Solution

by:andrew_man
andrew_man earned 250 total points
ID: 39694208
Can post your file here?
0
 
LVL 4

Expert Comment

by:andrew_man
ID: 39694515
File path incorrect!  Please dump the error screen to here.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 39694873
Which version of Excel is it? If 2007 or later, what format is the workbook - .xls, .xlsm, .xlsb?
0
 

Author Comment

by:iamnamja
ID: 39695527
Hey guys,

I found the issue.  There was another sheet where I was linking a text file (PL_ws), and when I tried the above code for some reason it was giving me an error.

What I did to get around this was to copy the results of the data that was linked to another sheet first, and query it through there.  Thanks for all your help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now