Forum Discussion

ArmandsBruns's avatar
ArmandsBruns
Frequent Contributor
12 years ago

Function (return arrays)


Hi,



I try return arrays from the following function but I got an error:

Unknown runtime error

Error location: "Array_Test_Data(x) = (VarToString(MSExcel.Cells(i, j)))"

----------------------------------------------

Function Test_Data(column)


 


  Dim Array_Test_Data(5), MSExcel  


 


  Set MSExcel = CreateObject("Excel.Application")


  MSExcel.Visible = True  


    


  x=0


  For i = 2 To 7


    For j = column To column


      Array_Test_Data(x) = (VarToString(MSExcel.Cells(i, j)))


      x=x+1


    Next


  Next


  


  Test_Data = Array_Test_Data


 


End Function

-------------------------------------------------



Armands

4 Replies

  • paul_scroce's avatar
    paul_scroce
    Frequent Contributor
    12 years ago


    Hi Armands



    If the workbook is already open then do you need to declare another MSExcel object in the function?



    These lines seem unnecessary if you pass the existing object to the function as a paramater.



     Dim MSExcel


      Set MSExcel = CreateObject("Excel.Application")


      MSExcel.Visible = True  

  • ArmandsBruns's avatar
    ArmandsBruns
    Frequent Contributor
    12 years ago
    Hi Paul,



    Thanks for your response.



    Excel open and everything works very well till "Array_Test_Data(x) = (VarToString(MSExcel.Cells(i, j)))"



    My test code:

    '------------------------------------------------------------

    Sun RunTest





      Dim MSExcel, Excel, Attr, Attr2


     


      Set Attr = Log.CreateNewAttributes


      Set Attr2 = Log.CreateNewAttributes


      Attr.Bold = True


      Attr2.Bold = True


      Attr2.FontColor=RGB(255, 0, 0)   


      


      Set MSExcel = CreateObject("Excel.Application")


      MSExcel.Visible = True


      


      If (Utilities.FileExists(Project.Variables.SourceTestData)) Then


        MSExcel.Workbooks.Open(Project.Variables.SourceTestData)


        Log.Message "Open -> '" &Project.Variables.SourceTestData &"',,,Attr


      Else


        Log.Error "Couldn't find -> '" &Project.Variables.SourceTestData &"'",,,Attr2


      End If


     


      MSExcel.Worksheets("User_Data").Activate



      Call Test_Data("7")





      data1 = Test_Data(0)        


      data2 = Test_Data(1)


      data3 = Test_Data(2)


      data4 = Test_Data(3)


      data5 = Test_Data(4)


      data6 = Test_Data(5)



    End Sub




    ----------------------------------------------

    Function Test_Data(column)


     


      Dim Array_Test_Data(5), MSExcel  


     


      Set MSExcel = CreateObject("Excel.Application")


      MSExcel.Visible = True  


        


      x=0


      For i = 2 To 7


        For j = column To column


          Array_Test_Data(x) = (VarToString(MSExcel.Cells(i, j)))


          x=x+1


        Next


      Next


      


      Test_Data = Array_Test_Data


     


    End Function

    -------------------------------------------------




    Regards

    Armands
  • ebodien's avatar
    ebodien
    Contributor
    12 years ago
    Hi Armands,



    You may not be able to get TestComplete and Excel to cooperate like that, I know I've had a lot of trouble trying to get TestComplete to step through an Excel file the way I needed it too.  The solution I've used is to import the Excel file into Access and use queries instead.  I created a dummy spreadsheet that I imported and using the following code read back all of column 7. (Data.mdb with the sheet becoming table User_Data)



    Sub main

        Call Test_Data("7")

    End sub

    Function Test_Data(column)

     

      Dim Array_Test_Data(5)

        

      x=0

      For i = 1 To 6

        For j = column To column

          Array_Test_Data(x) = Get_Data(i,j)

          x=x+1

        Next

      Next

      for y = 0 to ubound(Array_Test_Data)

        Log.Message(Array_Test_Data(y))

      Next

     

      Test_Data = Array_Test_Data

     

    End Function

     

    Function Get_Data(i,j)

    Dim Qry

      ' Create a query

      Set Qry = ADO.CreateADOQuery

      ' Specify the connection string

      Qry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _

      "Data Source=C:\Data.mdb"

        ' Specify the SQL expression

      Qry.SQL = "Select * FROM [User_Data] WHERE [User_Data].[ID] = :Num"

      ' Specify the parameter value

      Qry.Parameters.ParamByName("Num").Value = i

      ' Execute the query

      Qry.Open

      ' Process results and return the value to the calling routine

      Qry.First

      Get_Data = Qry.FieldByName(j).Value

      Qry.Close

    End Function



    I hope this will help.