none
Evaluating an If condition - variable not working RRS feed

  • Question

  • Hi Folks -

    I'm having trouble getting an If condition to work.

    The purpose of this script is check how many columns are in an excel workbook. 

    I pass in a number that the excel work book should be, I evaluate it again the total the vbs derives, and then exits with 1 if they differ or exit with 0 if there are no differences - and I'm capturing the return code via batch script. 

    However, the If logic isn't working at all:

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    
    Dim args, HC_PROCPATH, HC_EXCEL_SNAME, HC_EXCEL_CC
    set args = Wscript.arguments
    
    HC_PROCPATH= args(0)
    HC_EXCEL_SNAME= args(1)
    HC_EXCEL_CC= args(2)
    
    WScript.Echo HC_PROCPATH
    WScript.Echo HC_EXCEL_SNAME
    WScript.Echo HC_EXCEL_CC
    
    Set objWorkbook1= objExcel.Workbooks.Open( HC_PROCPATH & HC_EXCEL_SNAME )
    Set objWorksheet1 = objWorkbook1.Worksheets("Sheet1")
    Row_Count = objWorksheet1.UsedRange.Rows.Count
    Column_Count = objWorksheet1.UsedRange.Columns.Count
    
    'msgbox "Row Count is "&Row_Count& vbNewline &"Column Count is "&Column_Count
    
    If Column_Count <> HC_EXCEL_CC Then
    
    	WScript.Echo "BAD"
    	objWorkbook1.Save
    	objWorkbook1.Close
    	objExcel.Application.Quit
    	WScript.Quit 1
    	
    End If
    
    WScript.Echo "GOOD"
    objWorkbook1.Save
    objWorkbook1.Close
    objExcel.Application.Quit
    WScript.Quit 0
    

    Friday, June 16, 2017 3:59 PM

All replies

  •    Seems like you should do it like this:

        If Column_Count <> HC_EXCEL_CC Then
            WScript.Echo "BAD"
            WScript.Quit 1
        Else
            WScript.Echo "GOOD"
            WScript.Quit 0
        End If

        objWorkbook1.Save
        objWorkbook1.Close
        objExcel.Application.Quit

        
    Friday, June 16, 2017 4:55 PM
  • No - that doesn't work either. And it keeps the excel session open too.

    Thanks!

    Friday, June 16, 2017 5:26 PM
  •  The close and quit part should have come first:

        objWorkbook1.Save
        objWorkbook1.Close
        objExcel.Application.Quit

        If Column_Count <> HC_EXCEL_CC Then
            WScript.Echo "BAD"
            WScript.Quit 1
        Else
            WScript.Echo "GOOD"
            WScript.Quit 0
        End If


    Friday, June 16, 2017 5:34 PM
  • That doesn't work either - I don't know whats up.

    It's evaluating as if they are not equal, but they are.  Any ideas?

    Friday, June 16, 2017 7:27 PM
  • If I hard code the number on either side of <> it will work. Its not recognizing the variable - hmmmmmm
    Friday, June 16, 2017 8:04 PM
  • I found the issue:

    Needed to add CDbl to my variable evaluate as numbers, not strings.

    Friday, June 16, 2017 8:09 PM
  • Try casting them as longs.

    Oops - I see that you figured it out.

    Try to get into the habit of explicitly dimensioning your variables: There are all variants:

    Dim args, HC_PROCPATH, HC_EXCEL_SNAME, HC_EXCEL_CC

    These are all explicit:

    Dim args As Variant

    Dim HC_PROCPATH As String

    Dim HC_EXCEL_SNAME As String

    Dim HC_EXCEL_CC As Long


    Friday, June 16, 2017 8:13 PM