VB Error 2147467259

27458

Board Regular
Joined
Sep 20, 2004
Messages
71
I keep getting this error message when running in Office 2010. Works fine with earlier versions. I'm not VBA savy but using one a friend sent to me. If anyone could offer any solutions, it would be greatly appreciated. The XXX I changed for confidentiality of company name. Thanks.


Microsoft Visual Basic
Run-time error '-2147467259 (80004005)':
Method 'WaitForText' of object 'IWhllObh' failed

When Debug is seleted the following code is highlighted:
Call terminalEmulator.WaitForText("XXX XXX", 8, 33, 3)
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi, I have the same runtime error, and though there are a few other postings concerning it I found while searching there doesn't seem to be an answer that would help my situation, so I figured I would bump this thread

My code is too long to post in its entirety but the essence of what I am doing is having the user download one or multiple workbooks from a web applet, saving them, and then entering the name of the file(s) into my workbook, and executing the macro. This causes my code to open the files as follows
Code:
...
jobCounter = 11 'user input of job numbers starts here
FILE_PATH = "Analytical summary_Preservation\Saved Analytical Summaries\"
jobName = Cells(jobCounter, 1)
fileName = FILE_PATH & jobName & ".xlsx"
Workbooks("TestBook.xlsm").Sheets(2).Activate
maxCells = Cells(Rows.Count, 1).End(xlUp).Row
Do 
 errorFlag = False
 errorString = vbNullString
 Dim wb As Excel.Workbook '***
 Set wb = GetObject(fileName) '***
...
Loop
Set wb = Nothing
...

It goes on to parse the information in the wb object, uses that information to mark up another sheet within my workbook, and then performs a bunch of logical operations and creates a new sheet for each job it loops though. At the end of the loop I set wb = Nothing (though I'm not sure if that destroys the object and reclaims the memory, or just empties it, which is why I have the Dim in the loop, though I've removed this declaration and gotten the same behavior).

The code works perfectly as intended the first time (and ends appropriately if there is only one job). But, if necessary, when it loops back and tries to set wb = GetObject(fileName) again, I get the runtime error:

-2147467259 (80004005).

I'm pretty much completely stuck with this error and any help anyone can provide me with will be GREATLY appreciated! I have some limited C++ coding experience but my vba knowledge is pretty much a combination of that and googling, so I would definitely qualify as a total noob. Thanks so much in advance! :)
 
Upvote 0
It's likely to be caused by an unqualified reference somewhere, but it's not possible to be more specific without seeing the rest of your code.

As an aside, why can't you just Open the workbook rather than use GetObject?
 
Upvote 0
here is the full code, it is 3 functions. I'm sure it will make an experienced programmers head explode, which was part of my rationale in not posting it all initially. :laugh: I'll try to modify this to just use Application.Workbooks.Open, but I'm still curious as to what the problem is this way if something stands out. Thanks again!

Code:
Dim errorString As String
Dim errorFlag As Boolean
Sub GetErDone()
Dim fileName, cCode, FILE_PATH, jobName As String
Dim cCodeCounter, maxCells, jobCounter As Integer
'On Error GoTo Handler
Application.ScreenUpdating = False 'removes screen flicker, vastly improves performance
jobCounter = 11 'user input of job numbers starts here
FILE_PATH = "[URL="file://\\Centraln\Public\labs"]\\Centraln\Public\labs[/URL] shared\DWTS Shared\Analytical summary_Preservation\Saved Analytical Summaries\"
jobName = Cells(jobCounter, 1)
fileName = FILE_PATH & jobName & ".xlsx"
Workbooks("TestBook.xlsm").Sheets(2).Activate
maxCells = Cells(Rows.Count, 1).End(xlUp).Row
Do 'Loop on jobs
 errorFlag = False
 errorString = vbNullString
 Dim wb As Excel.Workbook
 Set wb = GetObject(fileName)
'Handler:
 'If Err.Number = 432 Then
  ' fileName = FILE_PATH & jobName & ".xls"
 'End If
 'Resume Next
 
 cCodeCounter = 18 'AS template starts listing analytes here
 Workbooks("TestBook.xlsm").Sheets(2).Activate
 Do 'loop on ccode
   ActiveCell = wb.Sheets(1).Cells(cCodeCounter, 9)
   cCode = Left(ActiveCell, 5)
 
   Workbooks("TestBook.xlsm").Sheets(2).Activate
 
   For i = 5 To maxCells
     If cCode = vbNullString Then
       Exit For
     End If
     If cCode = "C2023" Then 'BNA Scan is always combined with BNA and is unnecessary info
       Exit For
     End If
     If cCode = Cells(i, 6) Then
       Cells(i, 8) = 1
       Exit For
     End If
     If i = maxCells Then
        errorFlag = True  'if the c-code isn't found, displays error on final sheet
        errorString = errorString & cCode & ", "
     End If
   Next i
 
   cCodeCounter = cCodeCounter + 1
 Loop While cCode <> vbNullString
 Set wb = Nothing
 Call AddSheet(jobName)
 Call FillNewSheet(jobName)
 'reset sheet 2 after each job
 Workbooks("TestBook.xlsm").Sheets(2).Activate
 For i = 5 To maxCells
  Cells(i, 8).ClearContents
 Next i
 jobCounter = jobCounter + 1
 ActiveWorkbook.Sheets(1).Activate
 jobName = Cells(jobCounter, 1)
 fileName = FILE_PATH & jobName & ".xlsx"
Loop Until jobName = vbNullString
Application.ScreenUpdating = True
End Sub
Sub FillNewSheet(sheetName As String)
   Dim rowCount As Integer 'counter
   Dim maxRow As Integer   'sets upper bound for the for loop
   Dim nextRow As Integer  'row counter for insertion into sheet2
   Dim totalVolume, totalVOC, totalGC, totalHPLC, totalLCMS, totalRADS As Integer
   Dim totalEXT, totalWETCHEM, totalWAREHOUSE, totalTOC, totalMETALS As Integer
   Dim totalEXPOSURES, totalDWTS, boolGC1, boolGC2, boolVOC1, boolVOC2, boolVOC3 As Boolean
   Dim countHPLC, countVOC, total125, total250, total500, total1000 As Integer
   Dim totalLabels As Integer
   boolGC1 = True
   boolGC2 = True
   boolVOC1 = True
   boolVOC2 = True
   boolVOC3 = True
   totalEXPOSURES = False
   totalDWTS = False
   total125 = 0
   total250 = 0
   total500 = 0
   total1000 = 0
   countHPLC = 0
   countVOC = 0
   totalLabels = 0
   totalVOC = 0
   totalGC = 0
   totalHPLC = 0
   totalLCMS = 0
   totalRADS = 0
   totalEXT = 0
   totalWETCHEM = 0
   totalWAREHOUSE = 0
   totalTOC = 0
   totalMETALS = 0
   nextRow = 4 'first row based on the preset formatting
   Workbooks("TestBook.xlsm").Sheets(2).Activate
   maxRow = Cells(Rows.Count, 1).End(xlUp).Row  'returns bottom most non-empty cell in first column
 
   For rowCount = 5 To maxRow
      If Cells(rowCount, 8) <> 0 Then 'If user selected this row, copies the row then pastes into sheet 2
         Worksheets(2).Cells(rowCount, 1).Select
         ActiveCell.EntireRow.Select
         Selection.Copy
         Sheets(sheetName).Select
         Cells(nextRow, 1).Select
         ActiveSheet.Paste
         nextRow = nextRow + 1
         Sheets(2).Select
         If Cells(rowCount, 7) = "LCMS" Then
            totalLCMS = totalLCMS + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
            ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
            ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
            Else
               total1000 = total1000 + 1
            End If
         ElseIf Cells(rowCount, 7) = "RADS" Then
            totalRADS = totalRADS + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
         ElseIf Cells(rowCount, 7) = "EXT" Then
            totalEXT = totalEXT + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
            ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
            ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
            Else
               total1000 = total1000 + 1
            End If
         ElseIf Cells(rowCount, 7) = "TOC" Then
            totalTOC = totalTOC + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
         ElseIf Cells(rowCount, 7) = "METALS" Then
            totalMETALS = totalMETALS + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
         ElseIf Cells(rowCount, 7) = "WAREHOUSE" Then
            totalWAREHOUSE = totalWAREHOUSE + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
         ElseIf Cells(rowCount, 7) = "WETCHEM" Then
            totalWETCHEM = totalWETCHEM + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
            ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
            ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
            Else
               total1000 = total1000 + 1
            End If
         ElseIf Cells(rowCount, 7) = "EXPOSURES" Then
            totalEXPOSURES = True
         ElseIf Cells(rowCount, 7) = "DWTS" Then
            totalDWTS = True
            totalVolume = totalVolume + 150
         'HPLC and divisions
         ElseIf Cells(rowCount, 7) = "HPLC" And countHPLC = 0 Then
            If Cells(rowCount, 5) = "Y" Then
               totalHPLC = totalHPLC + Cells(rowCount, 2)
               totalLabels = totalLabels + Cells(rowCount, 2)
               totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
               If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
               ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
               ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
               Else
               total1000 = total1000 + 1
               End If
            Else
               If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
               ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
               ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
               Else
               total1000 = total1000 + 1
               End If
            countHPLC = 1
            totalHPLC = totalHPLC + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            End If
         ElseIf Cells(rowCount, 7) = "HPLC" And 15 > countHPLC And 0 < countHPLC Then
            If Cells(rowCount, 5) = "Y" Then
               totalHPLC = totalHPLC + Cells(rowCount, 2)
               totalLabels = totalLabels + Cells(rowCount, 2)
               totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
               If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
               ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
               ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
               Else
               total1000 = total1000 + 1
               End If
            Else
            countHPLC = countHPLC + 1
            End If
         ElseIf Cells(rowCount, 7) = "HPLC" And countHPLC >= 15 Then
            If Cells(rowCount, 5) = "Y" Then
               If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
               ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
               ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
               Else
               total1000 = total1000 + 1
               End If
               totalHPLC = totalHPLC + Cells(rowCount, 2)
               totalLabels = totalLabels + Cells(rowCount, 2)
               totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            Else
            countHPLC = 1
            totalHPLC = totalHPLC + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
               If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
               ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
               ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
               Else
               total1000 = total1000 + 1
               End If
            End If
         'GC and divisions
         ElseIf Cells(rowCount, 7) = "GC" Then
            totalGC = totalGC + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
            ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
            ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
            Else
               total1000 = total1000 + 1
            End If
         ElseIf Cells(rowCount, 7) = "GC-1" And boolGC1 = True Then
            boolGC1 = False
            totalGC = totalGC + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
            ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
            ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
            Else
               total1000 = total1000 + 1
            End If
         ElseIf Cells(rowCount, 7) = "GC-2" And boolGC2 = True Then
            boolGC2 = False
            totalGC = totalGC + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            If Cells(rowCount, 3) = 250 Then
               total250 = total250 + 1
            ElseIf Cells(rowCount, 3) = 125 Then
               total125 = total125 + 1
            ElseIf Cells(rowCount, 3) = 500 Then
               total500 = total500 + 1
            Else
               total1000 = total1000 + 1
            End If
         'VOC and divisons
         ElseIf Cells(rowCount, 7) = "VOC" And countVOC = 0 Then
            If Cells(rowCount, 5) = "Y" Then
               totalVOC = totalVOC + Cells(rowCount, 2)
               totalLabels = totalLabels + Cells(rowCount, 2)
               totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            Else
            countVOC = 1
            totalVOC = totalVOC + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            End If
         ElseIf Cells(rowCount, 7) = "VOC" And 0 < countVOC And countVOC < 6 Then
            If Cells(rowCount, 5) = "Y" Then
               totalVOC = totalVOC + Cells(rowCount, 2)
               totalLabels = totalLabels + Cells(rowCount, 2)
               totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            Else
            countVOC = countVOC + 1
            End If
         ElseIf Cells(rowCount, 7) = "VOC" And countVOC >= 6 Then
            If Cells(rowCount, 5) = "Y" Then
               totalVOC = totalVOC + Cells(rowCount, 2)
               totalLabels = totalLabels + Cells(rowCount, 2)
               totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            Else
               countVOC = 1
               totalVOC = totalVOC + Cells(rowCount, 2)
               totalLabels = totalLabels + Cells(rowCount, 2)
               totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
            End If
         ElseIf Cells(rowCount, 7) = "VOC-1" And boolVOC1 = True Then
            boolVOC1 = False
            totalVOC = totalVOC + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
         ElseIf Cells(rowCount, 7) = "VOC-2" And boolVOC2 = True Then
            boolVOC2 = False
            totalVOC = totalVOC + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
         ElseIf Cells(rowCount, 7) = "VOC-3" And boolVOC3 = True Then
            boolVOC3 = False
            totalVOC = totalVOC + Cells(rowCount, 2)
            totalLabels = totalLabels + Cells(rowCount, 2)
            totalVolume = totalVolume + (Cells(rowCount, 3) * Cells(rowCount, 2))
         End If 'end of bottle addition
       End If 'for loop
 
   Next rowCount
   'Display totals at bottom of new sheet
   Sheets(sheetName).Select
   nextRow = nextRow + 2
   Cells(nextRow, 1).Value = "TOTALS"
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total Volume:"
   Cells(nextRow, 2).Value = totalVolume
   Cells(nextRow, 3).Value = "mL"
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total VOC:"
   Cells(nextRow, 2).Value = totalVOC
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total TOC:"
   Cells(nextRow, 2).Value = totalTOC
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total HPLC:"
   Cells(nextRow, 2).Value = totalHPLC
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total GC:"
   Cells(nextRow, 2).Value = totalGC
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total Metals:"
   Cells(nextRow, 2).Value = totalMETALS
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total LCMS:"
   Cells(nextRow, 2).Value = totalLCMS
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total RADS:"
   Cells(nextRow, 2).Value = totalRADS
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total Extractions:"
   Cells(nextRow, 2).Value = totalEXT
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total Wetchem (non-TOC):"
   Cells(nextRow, 2).Value = totalWETCHEM
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total Number of Bottles to be shipped:"
   Cells(nextRow, 2).Value = totalWAREHOUSE
   If totalEXPOSURES = True Then
      nextRow = nextRow + 1
      Cells(nextRow, 1).Value = "CONTACT EXPOSURES ABOUT RVCM!"
      Cells(nextRow, 2).Value = 1
      totalLabels = totalLabels + 1
   End If
   If totalDWTS = True Then
      nextRow = nextRow + 1
      Cells(nextRow, 1).Value = "**MAKE SURE TO TAKE A pH READING!**"
      Cells(nextRow, 2).Value = 1
      totalLabels = totalLabels + 1
   End If
   nextRow = nextRow + 2
   If total1000 > 0 Then
      Cells(nextRow, 1).Value = "Total 1000 mL Amber:"
      Cells(nextRow, 2).Value = total1000
      nextRow = nextRow + 1
   End If
   If total500 > 0 Then
      Cells(nextRow, 1).Value = "Total 500 mL Amber:"
      Cells(nextRow, 2).Value = total500
      nextRow = nextRow + 1
   End If
   Cells(nextRow, 1).Value = "Total 250 mL Amber:"
   Cells(nextRow, 2).Value = total250
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total 125 mL amber:"
   Cells(nextRow, 2).Value = total125
   nextRow = nextRow + 1
   Cells(nextRow, 1).Value = "Total Labels:"
   Cells(nextRow, 2).Value = totalLabels
   nextRow = nextRow + 1
 
   If errorFlag = True Then
     Cells(nextRow, 1).Value = "The following C-codes were not recognized: "
     Cells(nextRow, 4).Value = errorString
   End If
   'Removes copied data from clipboard
   Application.CutCopyMode = False
End Sub
Sub AddSheet(sheetName As String)
ActiveWorkbook.Sheets("Template").Copy _
  after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Name = sheetName
Cells(1, 1) = sheetName
End Sub
 
Upvote 0
Anatana

Where do you get the error in the code?
 
Upvote 0
Anatana

Where do you get the error in the code?

I get it the second time through when I set wb = GetObject(fileName).

I actually just changed that bit based on andrew's advice to
Code:
Application.Workbooks.Open (fileName)
and am toying around with it but I'm still getting an error, though it's just a 400 error now...super frustrating
 
Upvote 0
Are you sure the file path is correct?

Their's no drive letter and if it's a UNC path it should begin with \\.
 
Upvote 0
Are you sure the file path is correct?

Their's no drive letter and if it's a UNC path it should begin with \\.
yeah, the constant string FILE_PATH starts with \\ and I always concatenate onto that. I think the issue may revolve around that though because some of the files are saved as .xls and some as .xlsx. That's what that commented out error correction was going to address. I'm trying to test it on files that I know for sure are .xlsx but it is still giving weird behavior. Some of my cells are being deleted randomly, i don't know, I may need to walk away from it and clear my head a bit
 
Upvote 0
I was looking at the original code where there is no \\.
 
Upvote 0

Forum statistics

Threads
1,225,761
Messages
6,186,894
Members
453,383
Latest member
SSXP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top