Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO access to excel workbook on website
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO access to excel workbook on website
I don't think ADO will let you connect over HTTP.
Tim "Dreiding" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving workbook to a website | Excel Programming | |||
Excel 2007 workbook not linking properly with authentic website | Excel Discussion (Misc queries) | |||
Export Excel Workbook to a Website | Excel Programming | |||
access a website from excel 2003 spreadsheet | New Users to Excel | |||
publishing semi-PROTECTED excel workbook as a dynamic website | Excel Programming |