Connecting to an Excel File saved in SharePoint

I need some help with code for connecting to an Excel file saved on a SharePoint server.

The code works fine here

Public Sub Read_Master()
Dim cn As ADODB.Connection
Dim adoRS As New ADODB.Recordset
 
Set cn = New ADODB.Connection
Dim sConnString As String
Dim sSQl As String
Dim sInput As String

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;"
sConnString = sConnString & "Data Source=C:\Documents and Settings\me\Desktop\TEMP_ODBC\A_Master.xls;"
sConnString = sConnString & "Extended Properties=" & """Excel 8.0;HDR=No;"""
 
cn.Open sConnString
  
End Sub

But following gives an error

Public Sub No_Read_Master()
Dim cn As ADODB.Connection
Dim adoRS As New ADODB.Recordset
 
Set cn = New ADODB.Connection
Dim sConnString As String
Dim sSQl As String
Dim sInput As String

'sLocation = "Data Source=http://SHAREPOINT/SHARE/prj/My_Team/Configuration/A_Master.xls;" 

sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;"
sConnString = sConnString & sLocation
sConnString = sConnString & "Extended Properties=" & """Excel 8.0;HDR=No;"""
cn.Open sConnString <-- Invalid Internet Address
 
End Sub

I can cut and paste the http string to a browser and it pulls up the Excel file fine.

I am using Excel 2003.

Any help will be much appreciated.

1 answer