ADO access to excel workbook on website

[Dreiding]

I have Excel 2003 and trying to use ADO to read data from an excel file
stored in a website folder without success.

The code below shows two definition of the "SourceFile". The second one
works.
When I use the first one the 'rsCon.Open szConnect' code fails.

Any thoughts, suggestion to make this work?

My code:

Option Explicit

Sub GetData()
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String

Dim SourceFile As String

SourceFile = "http://MyComputer.us.pat.com/iisfolder/My_Lookup_Table.xls"
SourceFile = "\\MyComputer.us.pat.com\iisfolder\My_Lookup_Table.xls"

'Create connection string
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
'sql query
szSQL = "SELECT * FROM [Sheet1$A2:A2];"

On Error GoTo Err_GetData

Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")

rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1

' Check to make sure we received data and copy the data
If Not rsData.EOF Then
MsgBox rsData.fields(0).Value
End If

' Clean up our Recordset object.
rsData.Close
rsCon.Close

Set rsData = Nothing
Set rsCon = Nothing
Exit Sub

Err_GetData:
MsgBox "The file name, Sheet name or Range is invalid of: " &
SourceFile, vbExclamation, "Error"
On Error GoTo 0
End Sub

Thanks,
- Pat

 

[Tim Williams]

I don't think ADO will let you connect over HTTP.

Tim