0

I am trying to query an Excel spreadsheet saved on SharePoint, like a database.
For some reason I am able to query the Excel spreadsheet perfectly, when it's a local spreadsheet, but not when it's on SharePoint.

I am able to use the Excel.Application object to open the Spreadsheet from VBScript in UFT, but not when I put the SharePoint link into my connection string data source. I've tried the following:

  • Changing slashes from / to \ and taking out white space by putting in %20 with no luck.

  • Changing "Data Source" to "Database", including trusted connection and including username and password. Here's my connection string/code:

    (Fails) ExcelFileLocation = "http://sharepointlocation/folders/spreadsheet.xlsx"

    (Works) ExcelFileLocation = "C:\projects\spreadsheet.xlsx"

When I do these lines, it does open the spreadsheet from SharePoint, but I am trying to avoid traversing through rows / columns to find the data I want.

TemExcel.Workbooks.Open "http://sharepointlocation/folders/spreadsheet.xlsx"
TempExcel.ActiveWorkbook.SaveAs "C:\projects\Temp.xlsx"
TempExcel.Application.Quit
objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelFileLocation & ";Extended Properties=""Excel 8.0;HDR=Yes;"";"   
objRecordSet.Open ExcelQuery,objConnection,adOpenStatic,adLockOptimistic,adCmdTest
Leigh
  • 28,765
  • 10
  • 55
  • 103
justin.cost
  • 1
  • 1
    You mentioned changing the direction of slashes didn't help. Have you tried using the UNC path to the SharePoint location? Something like `\\sharepointlocation\folder\spreadsheet.xlsx` (note the double slash and lack of a protocol prefix). Another option is to map a drive letter to the SharePoint library's UNC path and then try opening the file as you would a file on your local machine. – Thriggle Apr 13 '17 at 22:48
  • I just tried that and it works! Much appreciated. I thought I had already tried that, but I think I didn't get everything switched completely to UNC. Thanks again! – justin.cost Apr 17 '17 at 19:52
  • Great! I'll add it as an answer. (I just didn't want to propose it as answer if you'd already tried it and it hadn't worked.) – Thriggle Apr 17 '17 at 20:00
  • One additional thing I just noticed on the answer to the above issue, if you authenticate into SharePoint once before running the test in UFT (Opening above UNC path in File Explorer then typing in credentials to the Windows Security dialog that pops up) then it works, but if you restart your computer and haven't manually opened the UNC path in Fle Explorer to authenticate, it fails. I think I need to include authentication in my connection string? – justin.cost Apr 18 '17 at 15:05

1 Answers1

0

Use the UNC path to the SharePoint location.

Something like \\sharepointlocation\folder\spreadsheet.xlsx

(Note the double slash and lack of a protocol prefix).

Another option is to map a drive letter to the SharePoint library's UNC path and then try opening the file as you would a file on your local machine.

Thriggle
  • 7,009
  • 2
  • 26
  • 37
  • This works, except the following issue I'm having noted above in comment. I am getting stuck on Windows Security dialog that typically pops up when doing it manually. Do you know how to include Integration Security into a connection string when using Excel as data source? – justin.cost Apr 18 '17 at 18:25
  • Unfortunately, the UNC path uses the WebClient (WebDAV-based) service, which (according to [this question](http://stackoverflow.com/questions/38020887/vba-sharepoint-authentication-for-drive-mapping)) does not support the usual authentication cookie, hence the prompt. You could possibly use a startup script that would navigate to the desired SharePoint directory when the computer restarts (to try to force the authentication) but that might be too invasive for your end users. – Thriggle Apr 18 '17 at 19:01
  • Thanks for the heads up! I have a plan B to just open the file via the Excel.Application object and save to a temp file on local drive, which should take care of this. I was just trying to avoid that, but that will work just fine for the solution I need. Thanks for all your help! – justin.cost Apr 18 '17 at 19:06