• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1128
  • Last Modified:

Type mismatch code 800A000D when read cells.value

Hello everybody,
I wrote a vbscript code to read an excel file and I want the script to skip excel`s rows that has "#N/A" as value.
This is my code:
Sub ReadExcel(ExcelPath)
Dim objExcel
Dim CurRow=0
Dim worksheetCount
Dim currentWorkSheet
Dim Cells
Dim CellCM
Dim CellDate
Dim CellDest
Dim CellType
Dim CellVol
Dim CellCarrier
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = 0
objExcel.Workbooks.open excelPath, false, true

Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(1)
set Cells = currentWorksheet.Cells

  While Cells(CurRow+6,4).value<>""    'STOP WHEN IT GETS TO THE END OF LINES
      If Cells(CurRow+6,4).value<>"#N/A"  Then     'SKIP THOSE ROWS IN CASE VALUE=#N/A
         CellCM=Cells(CurRow+6,1).value
         CellCarrier=Cells(CurRow+6,2).value
         CellDest=Cells(CurRow+6,4).value
         CellType=Cells(CurRow+6,5).value
         CellVol=Cells(CurRow+6,6).value
         CellDate=Cells(2,6).value
           End If
          CurRow=CurRow + 1      
  Wend
Set currentWorkSheet = Nothing
REM We are done with the Excel object, release it from memory
Set objExcel = Nothing
End Sub

When it gets to read the cell with #N/A value, I get an error of type mismatch.
Anyone know why???
 
0
ggmisadmin
Asked:
ggmisadmin
1 Solution
 
cupCommented:
#N/A is not a string - it is an error condition.

Try an on error statement before the if statement and get it to go past the end if.
0
 
ggmisadminAuthor Commented:
If a put #N/A between quotations (" ") can not be considered as string?
What is the error code? Any err.number <>0 ?

tks
0
 
RobSampsonCommented:
Try using the IsNA function:

ReadExcel Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "Users.xls"

Sub ReadExcel(ExcelPath)
Dim objExcel
Dim CurRow
Dim worksheetCount
Dim currentWorkSheet
Dim Cells
Dim CellCM
Dim CellDate
Dim CellDest
Dim CellType
Dim CellVol
Dim CellCarrier
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = 0
objExcel.Visible = True
objExcel.Workbooks.open excelPath, false, True

CurRow = 0

Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(1)
set Cells = currentWorksheet.Cells
If objExcel.IsNA(Cells(CurRow+6,4)) = False Then
      CellValue = Cells(CurRow+6,4).value
Else
      CellValue = "NA"
End If
While CellValue<>""    'STOP WHEN IT GETS TO THE END OF LINES
      If objExcel.IsNA(Cells(CurRow+6,4)) = False  Then     'SKIP THOSE ROWS IN CASE VALUE=#N/A
            CellValue = Cells(CurRow+6,4).value
            CellCM=Cells(CurRow+6,1).value
            CellCarrier=Cells(CurRow+6,2).value
            CellDest=Cells(CurRow+6,4).value
            CellType=Cells(CurRow+6,5).value
            CellVol=Cells(CurRow+6,6).value
            CellDate=Cells(2,6).value
      Else
            CellValue = "NA"
    End If
      CurRow=CurRow + 1      
      If objExcel.IsNA(Cells(CurRow+6,4)) = False Then
            CellValue = Cells(CurRow+6,4).value
      Else
            CellValue = "NA"
      End If
Wend
Set currentWorkSheet = Nothing
REM We are done with the Excel object, release it from memory
Set objExcel = Nothing
End Sub


Regards,

Rob.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now