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

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

Tim

"Dreiding" <(E-Mail Removed)> wrote in message
news:33CDCA15-4613-454E-860B-(E-Mail Removed)...
>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