Forum Discussion

mrezahoseini's avatar
mrezahoseini
Contributor
9 years ago
Solved

How to read excel file from sharepoint in TestComplete

I have uploaded an excel file in sharepoint and would like to read its context.   How can I do it?   Thanks for your kind attention
  • EnergizerBunny's avatar
    EnergizerBunny
    9 years ago

    I find that this type of routine works for Excel files with the *.xlsx extension.

    It is in VBScript.

     

    Call the subroutine and pass in the fully qualified file path in the variable 'svFile'

     

     

    Sub ReadDataFromExcel(svFile)
        Dim Excel, RowCount, ColumnCount, r, c

        Set Excel = Sys.OleObject("Excel.Application")
        Excel.Workbooks.Open(svFile)

     

        RowCount = Excel.ActiveSheet.UsedRange.Rows.Count
        ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count

        For r = 1 To RowCount
            For c = 1 To ColumnCount
                Log.Message "Cell row:" & r & " col:" & c & " = " & VarToString(Excel.Cells(r, c))
            Next
        Next

     

        Excel.Quit
        Set Excel = Nothing
    End Sub

EnergizerBunny's avatar
EnergizerBunny
Contributor
9 years ago

I find that this type of routine works for Excel files with the *.xlsx extension.

It is in VBScript.

 

Call the subroutine and pass in the fully qualified file path in the variable 'svFile'

 

 

Sub ReadDataFromExcel(svFile)
    Dim Excel, RowCount, ColumnCount, r, c

    Set Excel = Sys.OleObject("Excel.Application")
    Excel.Workbooks.Open(svFile)

 

    RowCount = Excel.ActiveSheet.UsedRange.Rows.Count
    ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count

    For r = 1 To RowCount
        For c = 1 To ColumnCount
            Log.Message "Cell row:" & r & " col:" & c & " = " & VarToString(Excel.Cells(r, c))
        Next
    Next

 

    Excel.Quit
    Set Excel = Nothing
End Sub

  • mrezahoseini's avatar
    mrezahoseini
    Contributor
    9 years ago

    Thanks for your prompt reply

     

    I would like to work with excel files which are stored on the web server.

     

    How can I access to its sheet and contents of its sheet?

     

    Thanks for attention:)

  • mrezahoseini's avatar
    mrezahoseini
    Contributor
    9 years ago

    Thank you really too much

     

    That works for me:)