none
Pass Multidimensional Array From Excel VBA to WebService RRS feed

  • Question

  • My requirement is to call webservice from excel vba.

    First, I am getting range of cell value from excel as below:

    Dim Arr As Variant
    Arr = Range("A35:G45")

    then, i am passing it to my webservice as a parameter as below:

    Dim XMLHttp As Object: Set XMLHttp =CreateObject("Microsoft.XMLHTTP")
    XMLHttp.Open "POST", "http/InsertLaborCostDetail", False
    XMLHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    XMLHttp.send "Array=" & Arr

    And in my webservice, i am getting values in object type

    [WebMethod]
        public object[][] InsertLaborCostDetail(object[][] Array)
        {
            return Array;
        }

    but i am getting Type Mismatch error.

    can anyone please help me on this?

    Wednesday, January 21, 2015 11:59 AM

Answers

  • Hi Prashant,

    You got the "Type Mismatch" error in this line, right?

    >>XMLHttp.send "Array=" & Arr

    You can't concatenate a String and a Range object, Range object can't be parsed to a string implicitly. Please take danielsparkk's advice, use JSON format string to send to the web service server. Or just concatenate the values in the cells with some splitter, for example, split columns with "," and split rows with "|".

    Dim Arr As Range
        Dim StringToSend As String
        
        Set Arr = ActiveWorkbook.ActiveSheet.Range("A1:B4")
        
        For i = 1 To Arr.Rows.Count
            For j = 1 To Arr.Columns.Count
                StringToSend = StringToSend & Cells(i, j).Value & ","
            Next
            StringToSend = Left(StringToSend, Len(StringToSend) - 1)
            StringToSend = StringToSend + "|"
        Next
        StringToSend = Left(StringToSend, Len(StringToSend) - 1)
    
        Dim XMLHttp As Object: Set XMLHttp = CreateObject("Microsoft.XMLHTTP")
        XMLHttp.Open "POST", "http/InsertLaborCostDetail", False
        XMLHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        XMLHttp.send "Array=" & StringToSend

    on the server side, also receive the parameter as a string:

    [WebMethod]
            public string[][] InsertLaborCostDetail(string Array)
            {
                List<string[]> result = new List<string[]>();
                string[] temp1 = Array.Split('|');
                foreach (string t in temp1)
                {
                    
                    result.Add(t.Split(','));
                }
    
                return result.ToArray();
            }


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 23, 2015 8:50 AM
    Moderator
  • Then I would recommend that you upload this workbook through the web service or a web page and process the workbook data on the server side. Use Open XML SDK to read the data.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 23, 2015 9:55 AM
    Moderator

All replies

  • try to use json format data but not the range object. XMLHttp.send actually send a string but not a complex object to the service.

    Dim dataToSend as String

    dataToSend="Array=" & Arr

    debug and check what dataToSend is, then you'll find the root cause of the problem.

    Thursday, January 22, 2015 8:47 AM
  • Hi Prashant,

    You got the "Type Mismatch" error in this line, right?

    >>XMLHttp.send "Array=" & Arr

    You can't concatenate a String and a Range object, Range object can't be parsed to a string implicitly. Please take danielsparkk's advice, use JSON format string to send to the web service server. Or just concatenate the values in the cells with some splitter, for example, split columns with "," and split rows with "|".

    Dim Arr As Range
        Dim StringToSend As String
        
        Set Arr = ActiveWorkbook.ActiveSheet.Range("A1:B4")
        
        For i = 1 To Arr.Rows.Count
            For j = 1 To Arr.Columns.Count
                StringToSend = StringToSend & Cells(i, j).Value & ","
            Next
            StringToSend = Left(StringToSend, Len(StringToSend) - 1)
            StringToSend = StringToSend + "|"
        Next
        StringToSend = Left(StringToSend, Len(StringToSend) - 1)
    
        Dim XMLHttp As Object: Set XMLHttp = CreateObject("Microsoft.XMLHTTP")
        XMLHttp.Open "POST", "http/InsertLaborCostDetail", False
        XMLHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        XMLHttp.send "Array=" & StringToSend

    on the server side, also receive the parameter as a string:

    [WebMethod]
            public string[][] InsertLaborCostDetail(string Array)
            {
                List<string[]> result = new List<string[]>();
                string[] temp1 = Array.Split('|');
                foreach (string t in temp1)
                {
                    
                    result.Add(t.Split(','));
                }
    
                return result.ToArray();
            }


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 23, 2015 8:50 AM
    Moderator
  • Thanks CailenZhong for your advice.

    But what happens if i have 10,000rows in excel and i want to pass it to webservice?

    Friday, January 23, 2015 9:37 AM
  • Then I would recommend that you upload this workbook through the web service or a web page and process the workbook data on the server side. Use Open XML SDK to read the data.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 23, 2015 9:55 AM
    Moderator