VBA run-time automation error

[dzugel]

Hi - I have a macro that is supposed to open a page on a web site, select and copy the entire contents of that page, then paste into a new workbook. It works properly the first or second time I run Excel after a system restart. After that I receive an error stating: Run-Time Error '-2147417848 (80010108)': Automation error. The object invoked has disconnected from its clients. I uploaded and attached a screen shot of this error window. Once it occurs the first time, I get this error every other time on the connection .REFRESH command until I close down and restart my system. I'm wracking my brain trying to figure out why it works one or two times then stops. I've tried clearing IE cache and changing the IE page view setting to refresh the page on every visit thinking therre may be a link between how IE works the web and how my VBA code is interacting with the web, to no avail. I've searched Microsoft knowledgebase for this error and see references to it, but nothing seems related to getting this error when using the statement With Worksheets(1).QueryTables.Add(Connection:=... Does anyone have a clue what might be happening, or what else I can try to get around this problem? Thanks.

[RobDog888]

Welcome to the Forums.

Can you post your Excel related code?

[dzugel]

Here's the beginning of the macro...

Sub CreateNewAliasTable()
'
' CreateNewAliasTable Macro
' Macro recorded 3/24/2005 by DZugel
'
Dim i, j, k, l As Integer
Dim iHeadRow, iSplitRow, iLastRow, iLastCol As Integer
Dim iFirstRow, iSecondRow As Integer
Dim sPath, sSubPath, sHostname, sFile, sVanstat, sTemp As String
'
sPath = Application.DefaultFilePath
sSubPath = "Projects\Patrol"
sHostMaster = "Host Code Master"
sFile = sPath & "\" & sSubPath & "\" & sHostMaster & Format(Date, " yyyy mmdd") & ".xls"
sVanstat = "URL;http//techweb.xxxx.com/team/abc/abcstat.htm"
Workbooks.Add
With Worksheets(1).QueryTables.Add(Connection:=sAbcstat, Destination:=Range("Sheet1!A1"))
.Name = "abcstat"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Sheets("Sheet1").Name = "HostAliasTable"

I assign the macro to an icon on my toolbar. As you can see, its only the third statement after the declarations and initialization code. There is a lot more code following this, and will include it if needed, but it mainly deals with formatting the imported data. The .Refresh BackgroundQuery:=True statement is where the system fails when I step through.

[dzugel]

One more thing, the basic structure of this code came from the Record macro feature within Excel itself.

[MJBNET]

try setting it to false...see if works...that's how i do it...

The .Refresh BackgroundQuery:=True

[dzugel]

Good morning - I tried setting the code to:

.Refresh BackgroundQuery:=False

with no different results. I immediately get the same error window. After the first time, though, I get a Run-time error '1004' (see attached). Can anything further down in the code file be causing these errors? This is a tough one. Thanks for any further help.

[Ecniv]

Your first post suggests that the connection object you are using is closed by something at some point.

Error 1004 is a nonspecific error and is not very useful (to you or me).
If background query is now erroring in your code, then change it back to true.

See if the connection object is still open before refreshing? (debug mode ... breakpoint in the code)

[dzugel]

OK, I added some DEBUG.PRINT code after reworking the connection statement. It now looks like this:

With Worksheets(1).QueryTables.Add(Connection:=sVanstat, Destination:=Range("Sheet1!A1"))
.Name = "vanstat"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
End With
Debug.Print Worksheets(1).QueryTables("vanstat").Name
Debug.Print Worksheets(1).QueryTables("vanstat").Connection
Debug.Print Worksheets(1).QueryTables("vanstat").MaintainConnection
Debug.Print Worksheets(1).QueryTables("vanstat").QueryType
Debug.Print Worksheets(1).QueryTables("vanstat").WebSelectionType
Debug.Print Worksheets(1).QueryTables("vanstat").SourceDataFile
With Worksheets(1).QueryTables("vanstat")
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Name = "HostAliasTable"

The strange thing is that it now works most of the time. But once a problem develops I must shut down Excel and restart before I can try to run the macro again. Does Excel keep a connection open if this kind of error is found during processing so that the macro cannot be rerun until Excel is closed? Any idea why it seems to be working now even though the only changes to the macro are the DEBUG.PRINT lines and moving the REFRESH statement to a separate WITH statement? Confused, but not so frustrated, in Atlanta.

[dzugel]

I found something else out that is even more strange. Right after I sent the last post, I turned off all debugging and the Immediate Window and tried to run the macro again. It immediately failed. I shut down IE and Excel, restarted Excel, tried again and it still failed. So I reactivated Breakpoints at the Workbook.ADD and the first DEBUG.Print statements, started the macro, hit F8 to step through the code, and voila - it worked again. I reran it then 4 or 5 times, each time stepping through the code, and it worked. Can this error be a timing problem of some kind since it seems to work when I step through the code but can't get it to work when I take all debugging out? Still unsure what to try next.

[Ecniv]

Before you do the refresh - try puting one or two DoEvents (this hands windoze the control for a turn) which might help.

Also, when you say it errors without the breakpoint mode, can you pinpoint where it errors?
Do you use error handling (ignoring?) ?
If you leave the debug.print bits in but don't use the breakpoints, does it still crash?

Perhaps closing off any connections would help?

[dzugel]

When it errors it is always on the .Refresh BackGroundQuery=False statement.

I tried keeping the DEBUG.Print statements in with no breakpoints and it still errors.

I do not use any error handling.

How do I close a connection? I have no other connections open except this one accessing a URL.

I added first one, then two, DoEvents statements and the macro still errors. I put one DoEvents just before my DEBUG.Print statements and one just after them, but before the .Refresh statement.

[MJBNET]

! BACKUP YOUR STUFF ! before you try my stuff...this may sound stupid, but look in the name box..i have encountered that add(connection adds names...each time...it stuffs it all up...if possible try your macro on a new worksheet(or QueryTable.Delete)...see if any difference....mine imports from local files this is what i use each day 12,000 times daily and it is as faster than "Workbooks.OpenText" if i remember right i use the 'on error resume next because the local file may not exist... now from online should not matter
AND BE CAREFUL as this is specific to my sheets...I don't use names or labels...SO BACKUP YOUR STUFF!!!!!!

'IMPORT TEXT FILE DATA
    'TURN OFF LABELS TO PREVENT PROBLEMS WITH THIS FUNCTION
    'THEN TURN ON IMMEDIATELY AFTER THIS FUNCTION
    'THIS WORKBOOK USES NO LABELS SO NO PROBLEM
    ActiveWorkbook.AcceptLabelsInFormulas = False
    
    FILEADDRESS = "TEXT;C:\StockBook\TDB\130xD\" & THISSTOCK & ".txt"
    On Error Resume Next    'ERRORS WILL SHOW UP IN DATE MATCHING
    With ActiveSheet.QueryTables.Add(Connection:= _
        FILEADDRESS, Destination:=Range("A1137"))
        .Name = ""
        .FieldNames = False
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierNone
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False 'CHANGE THIS FOR NOW TO FALSE (3/13/05)
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.AcceptLabelsInFormulas = True
    Cells.Select
    Selection.QueryTable.Delete
    QnamesDelete

Sub QnamesDelete()
    Dim NM As Name
    For Each NM In ActiveWorkbook.Names
    NM.Delete
    Next
End Sub

[MJBNET]

The strange thing is that it now works most of the time. But once a problem develops I must shut down Excel and restart before I can try to run the macro again. Does Excel keep a connection open if this kind of error is found during processing so that the macro cannot be rerun until Excel is closed? Any idea why it seems to be working now even though the only changes to the macro are the DEBUG.PRINT lines and moving the REFRESH statement to a separate WITH statement? Confused, but not so frustrated, in Atlanta.

i think you might have answered yourself here...excel's thinking is your friend or enemy...you will encounter this with formattimg in cells...like date stuff...here BACKUP YOUR WORK....THEN [QueryTable.Delete] or try your macro on a new sheet , workbook, whatever... i think by hand you select all cells then delete...you get a message if ya wanna bla bla...kill it all... now retry.. lemme know...there is now doubt there is a way to to get yur stuff on yur sheet

[dzugel]

There are no NAMES defined prior to beginning this macro, and no Workbooks are open. This macro creates a new Workbook then tries to open a single HTML document on an internal web site using a URL connection. There are no other sheets or workbooks open and I start the macro immediately after I begin Excel. There is no formatting in the sheet. I tried the QueryTable.Delete command with no success. I keep thinking there is something wrong with the URL connection, but it at least appears to work. As I step through the code and step into the REFRESH command, I see the system connect to the page. Then it usually fails, but will work properly sometimes and retrieve the entire page as needed. I think it's about time to stop trying to completely automate this process and just manually retrieve the data, paste it into the sheet, and have the macro to the necessary formatting. Unless there is something else to try.

[dzugel]

Thanks to all who offered ideas and assistance while I worked on this problem. It never was resolved and continues today. I'm giving up trying to fix the automatation and will handle this task manually. Case closed!

[harrycunningham]

Hi,

I'm new to this thread but I had a similar problem and believe I've figured out the problem.

My process allowed me to change the selection criterions before it run. However, one of the values selected was defined as a global variable and was modified during the process. In my case it was a date value which was selected out of the database in 'mm/dd/yyyy' format and I needed to do a comparison on a 'dd/mon/yy' date. I converted the input data to 'dd/mon/yy' and this worked fine the first time, however, the next time it ran the new format was maintained and caused the conversion to the new format to fail.

I tracked down my problem by displaying the sql before it was processed.
I added....

Sheets("BASE_DATA").Select
Worksheets("BASE_DATA").Activate
Range("A3501").Select
ActiveCell.FormulaR1C1 = "" & sql


to the front of my 'run_sql' routine. When the 'Refresh BackgroundQuery:=False' was returned I was then able to see the sql causing the problem and work my way back with the de-bugger tool.

I hope this helps.

[dmaruca]

Hey way to go bumping something almost a year old. I managed to read it some before I realized how old it was. Looks like the problem could have been solved by turning Option Explicit on. He assigned a connection like so..

Connection:=sAbcstat

but that variable was never declared or assigned. He later changed it to ...

Connection:=sVanstat

and said it started working and errored somewhere else. That's just before he gave up. This could have been fixed. I wonder if he's still around.

[harrycunningham]

Yes, it is an old call, however it helped me to get an idea of what to look for and I saw that dzugel had given up so I thought he/she might still be out there doing the process manually. If not I'm sure someone else will come across this problem.

[jeng02]

I came across the same error in VBA, and I was just trying to refresh a querytable in my ThisWorkbook object Workbook_Open event. Thanks to you all, all I had to do was add "Option Explicit" at the top and for some reason the error went away. For today, anyway. I hope it doesn't pop up again at some point.
It may also have something to do with the backgroundquery T/F thing.

[aubie8]

Hi, even after all these years.....

I am getting the same error and cannot figure out the problem. If I use F8 to step through my code, I can just hold it down and let it run as fast as it can, it works, but as soon as I click run, it errors out with the run-time error and locks up Excel and I have to kill it through Task Manager. I tried the Option Explict, that didn't seem to work.

Any other ideas?

[jeng02]

I thought that "option explicit" helped, but I have continued to run in to this problem over the past year. I am posting my final workaround (not solution, really) here:

This code goes in the "ThisWorkbook" object on the workbook:

Private Sub Workbook_Open()
    'Delay it for late binding??
    Application.OnTime Now + TimeValue("00:00:03"), "StartupRefresh"
End Sub

Put this code in a module in the workbook:

Sub StartupRefresh()
     Dim IsSaved As Boolean

     IsSaved = ThisWorkbook.Saved
  'I get the error when I try to do this.  You may be trying to do something different...
     ThisWorkbook.Sheets("DBQuery-Workdays").QueryTables(1).Refresh
     If IsSaved Then ThisWorkbook.Saved = True

End Sub

Basically, I am scheduling a delayed run of whatever I'm trying to do when I get the automation error.

Let me know if it works for you!

[tgla]

jeng02,
Thank you for the post. I had the same problem. it appears the connection requires a nano second or two, and the latency provided by using the statement and the sub() below solves the issue.

Application.OnTime Now + TimeValue("00:00:03"), "RefreshTable(qt)"

Sub RefreshTable(qt As QueryTable)
qt.Refresh
End Sub

Thank you sincerly,
tgla

[jeng02]

I'm glad it worked for you. I've been helped so many times on forums like these, it's nice to be able to help someone else for a change!

[Yana]

Hey! Huge thanks to you all, I had the same problem, and your ideas helped me to find the solution, which is really works for me!

After finishing With block add DoEvents

it should be smth like this:

With
'............
'your code with adding and refreshing table
'...........
End With
DoEvents

Know that this topic is too old, but maybe it also helps someone