OBDC Connection to an external Excel file located on a Sharepoint webserver

Hello world!

I am digging myself into the topic of database connections and unfortunately I am stuck.

Problem:
On a sharpoint webserver I have two files: source.xlsx and analysis.xlsx. The source file contains a table with various entries, which are updated on a weekly basis, new entries are added, etc. With the analysis file the source file is analysed. Different sharepoint members should have access to the analysis file. The source file is just maintained by me. In a later stage it is possible that other people need to have access to the source file, too.

So far so good:
Having learned that Excel links like =[source.xlsx]Table!A1 require that both excel files need to be opened I found out that I can overcome this by using database connections (e.g. ODBC). That seem to work so far.

In analysis.xlsx I established a database connection with "Microsoft Query" and referenced it to source.xlsx. Therefore I needed to map my Sharepoint folder as network drive (Z:). Why is that necessary?
However the connection works, I am able to refresh the data - everything's fine... BUT

As soon as I disconnect the network drive which is connected to my sharepoint folder the database connection does not work anymore and I get an error message. My colleagues who access the analysis.xlsx do not have mapped network drives to the Sharepoint folder either and I think there must be another approach possible than getting all colleagues to map the drive as I did.

The connection string is:

DSN=Excel Files;DBQ=Z:\Quelldaten.xlsx;DefaultDir=Z:\;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

It tried to enter the webserver instead of the drive letter:

DSN=Excel Files;DBQ=http://meinSharepointserver.de/Documents/Quelldaten.xlsx;DefaultDir=http://meinSharepointserver.de/Documents/;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

But that is just rejected stating "Invalid Internet address".

What can I do?

Best
Flipp

2 answers

DSN=Excel Files;DBQ=Z:\source.xlsx;DefaultDir=Z:\;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

DSN=Excel Files;DBQ=http://mySharepointserver.com/Documents/source.xlsx;DefaultDir=http://mySharepointserver.com/Documents/;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

DSN=Excel Files;DBQ=//mySharepointserver.com/Documents/source.xlsx;DefaultDir=//mySharepointserver.com/Documents/;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;