Error reading Excel at #VALUE!

[rrs123]

Hello,

I am trying to read an Excel file using vbscript. However, at specific cell where the value in the cell is #VALUE! because of failed VLOOKUP call, the vbscript fails with following error:
Microsoft VBScript runtime error: Type mismatch

How should I handle this?

Following is my code:

Option Explicit

Dim intLoopCount, objExcel, objWorkbook,currentWorkSheet,usedColumnsCount,usedRowsCount
Dim quote

Set objExcel = CreateObject("Excel.Application")


Set objWorkbook = objExcel.Workbooks.Open("X:\Inventory.xlsx")
'workSheetCount = objExcel.Worksheets.Count
'WScript.Echo "We have " & workSheetCount & " worksheets"

Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(1)
'usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count
'usedRowsCount = currentWorkSheet.UsedRange.Rows.Count
'WScript.Echo "Rows: " & usedRowsCount
intLoopCount = 2
quote = """"
Do Until intLoopCount > 4600
WScript.Echo objExcel.Cells(intLoopCount,20)
intLoopCount = intLoopCount + 1
loop
objExcel.Workbooks(1).Close
objExcel.Application.Quit
objExcel.quit
objExcel = Empty
objWorkbook = Empty

Thanks in Advance

RRS

 

[mrmovie]

i presume it is failing here:

WScript.Echo objExcel.Cells(intLoopCount,20)

you can trying to implicitly convert objExcel.Cells(intLoopCount,20) to Text....presuming #VALUE! really equate to Null? prehaps being more explicit will help?

WScript.Echo objExcel.Cells(intLoopCount,20).Value???
WScript.Echo objExcel.Cells(intLoopCount,20).Text???
'''
strTemp = ""
If Not IsNull(objExcel.Cells(intLoopCount,20)) Then
strTemp = objExcel.Cells(intLoopCount,20).Value
Else
'what to do when it is Null?
strTemp = "#VALUE!"
End If