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
10 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
Contributor
10 years ago
For example I would like to use the content of this excel file, which is located in the sharepoint:

var Driver = DDT.ExcelDriver("http://shpnt07/...../Add-Dastresi.xls", "Sheet1");

FindRow (Grid, "name", Driver.Value(0));

DDT.CloseDriver(Driver.Name);

Unfortunately, I couldn't access to this excel, how can I cope with it?

Thanks
- TanyaYatskovska
  SmartBear Alumni (Retired)
  10 years ago
  Hi Mahnazrezahoseini,
  
  You cannot pass URL to DDT.ExcelDriver.
  
  Is it possible that you download the Excel file before working with it in your test? In this case, you would use DDT feature to read the file content.
  - SmartBearDan
    SmartBear Alumni (Retired)
    10 years ago
    Hi Mahnazrezahoseini,
    
    It may be possible to download the file from Sharepoint using a script like the one in the "How To" article below. Once the file is downloaded you can then use the Data Loop like Tanya mentioned.
    
    How to download a file from the Internet: http://support.smartbear.com/viewarticle/8999/
    
    Reference Links:
    Running a script routine from within a Keyword Test: http://support.smartbear.com/viewarticle/56271/
    Data Driving a Test Case in TestComplete: http://support.smartbear.com/screencasts/testcomplete/creating-data-driven-tests/
    
    Hope that helps,
    Dan
EnergizerBunny
Contributor
10 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
  Contributor
  10 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
  Contributor
  10 years ago
  Thank you really too much
  
  That works for me:)