Forum Discussion

rbhogavalli's avatar
rbhogavalli
Occasional Contributor
11 years ago

Receiving Type mismatch error

Code:


Sub Generate_Xmls


Dim ColName(), new_nodes() 


Dim Nodes()        'Array storing the current splited node names


Dim NodeStack()    'Array storing the last node names


 


' the default node delimiter


Const Node_Sep = "/"


'Open Excel Connection


Set Excel = Sys.OleObject("Excel.Application")


Excel.Workbooks.Open("Z:\Automation\Auto Programs, Code base & Internal Tools\Auto tools\Generate_XML.xlsm")


Delay 1000


Excel.Worksheets("UPS").Activate


'   Discover dimensions of the data we  will be dealing with...


ColCount = Excel.ActiveSheet.UsedRange.Columns.Count


ReDim ColName(ColCount) ' The Array of column names


RowCount = Excel.ActiveSheet.UsedRange.Rows.Count


 


 


If RowCount >= 1 Then


    '   Loop accross columns... and put names in array


    For i = 1 To ColCount


      '   Mark the cell under current scrutiny by setting an object variable...


      Set rngCell = Excel.ActiveSheet.Cells(1,i)


'  not support merged cells .. so quit


' If Not.rngCell.MergeArea.Address = _


'                           rngCell.Address Then


' MsgBox ("!! Cell Merged ... Invalid format")


'            Exit Sub


' End IF


ColName(i) = rngCell.Text


    Next


End If


 


  ReDim NodeStack(0),new_nodes(0)


   'Loop down the table's rows


    For i = 2 To RowCount


      ' Create the XML DOM object


   Set XMLDoc = Sys.OleObject("Msxml2.DOMDocument.4.0")


'CreateObject("Microsoft.XMLDOM")


   


      ' NODE_PROCESSING_INSTRUCTION(7) --- reference http://www.devguru.com/Technologies/xmldom/quickref/obj_node.html


   Set Heading = XMLDoc.createNode(7, "xml", "")


   XMLDoc.appendChild (Heading)


    


   ' Set the root node


   Set top_node = XMLDoc.createNode(1, "SHIPMENT", "")


   top_node.setAttribute "ACTION", "SHIP"


   XMLDoc.appendChild (top_node)


    


      'Loop accross columns...


      For j = 3 To ColCount


      


          'Mark the cell under current scrutiny by setting an object variable...


          Set rngCell = Excel.ActiveSheet.Cells(i,j)


                


          'Is the cell merged?..


'          If Not rngCell.MergeArea.Address = rngCell.Address Then


'                MsgBox ("!! Cell Merged ... Invalid format")


'                Exit Function


'          End If


                         


          ' divide the field name by the delimiter to get appropriate node names


          Nodes = Split(ColName(j), Node_Sep )




Receiving a type mismatch error. Can anyone help me on this :(

  • HKosova's avatar
    HKosova
    11 years ago
    Short answer: change

    to





    Long answer: Use Dim with parentheses only if you assign array elements manually, like this:



    Dim arr(5)

    For i = 0 To 5

      arr(i) = i

    Next

    If you use arrays returned by functions or subroutines, you need to Dim array variables without parentheses:



    Dim arr

    arr = Split("a b c", " ")

  • HKosova's avatar
    HKosova
    SmartBear Alumni (Retired)
    11 years ago
    Short answer: change

    to





    Long answer: Use Dim with parentheses only if you assign array elements manually, like this:



    Dim arr(5)

    For i = 0 To 5

      arr(i) = i

    Next

    If you use arrays returned by functions or subroutines, you need to Dim array variables without parentheses:



    Dim arr

    arr = Split("a b c", " ")

  • rbhogavalli's avatar
    rbhogavalli
    Occasional Contributor
    11 years ago
    I figured it out but thanks for the long explanation it helped me when i should use declaration type