excel ADO Extended Parameter Passing Fails with Spaces in http path.

ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=Excel 8.0;"

The file is located at:

http:\\folder\folder%20again\filename.xsl

Sub myXLRecordSet()
Dim myADOConn As ADODB.Connection
Dim myADORs As ADODB.Recordset
Dim mySQL As String
Dim myConn As String

Line10:
    Set myADOConn = New ADODB.Connection
    Set myADORs = New ADODB.Recordset
    myConn = "DSN=Excel Files;" & _
        "DBQ=C:\sampledata.xls;" & _
        "DefaultDir=C:;" & _
        "DriverId=790;MaxBufferSize=2048;PageTimeout=5 ;"
Line20:
    mySQL = "SELECT T1.*"
    mySQL = mySQL & " FROM myDBase T1"

Line30:
    myADOConn.Open myConn
    myADORs.Open Source:=mySQL, ActiveConnection:=myADOConn

    If Not (myADORs.BOF Or myADORs.EOF) Then
        myADORs.MoveFirst

        Do While Not myADORs.EOF
            ActiveCell.Offset(0, 0).Value = myADORs.Fields(0).Value
            ActiveCell.Offset(0, 1).Value = myADORs.Fields(1).Value
            myADORs.MoveNext
            ActiveCell.Offset(1, 0).Select
        Loop
        myADORs.Close
        myADOConn.Close
    Else
        MsgBox ("No Records to Display, please drink some Cocoa")
    End If

Application.ScreenUpdating = True


    Set myADORs = Nothing
    Set myADOConn = Nothing
End Sub

I understand the concept of pulling and incerting data via ADO, the problem is that in the MS SharePoint environment the "Data Source=" & ThisWorkbook.FullName & ";" & _ part of the ADO fails when it trys to pass the http:\\ with spaces. It remoces the %20 and leaves the space.

Example:
http:\\something%20Something\filename.xls

I'm not sure if this will work, but try putting this bit of code in first,

myDSource = ThisWorkbook.FullName
myDSource = Replace(myDSource, "%20", Chr$(37) & "20")
ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & myDSource & ";" & _
    "Extended Properties=Excel 8.0;"

What results do you get?

Matt

I get this error. "Invalid Internet Address" when the connectionstring is passing."http://teamsites.fm.ith.xxxx.com/sites/SOX_Prog_Mgmt/Mgmt%20Reports/Q2/STestOX_2005_indicators_data.xls"

SQL = "SELECT COUNT(*) FROM [TestingDetail$]" & _
        "WHERE TestStatus = 'Complete' AND DocTitle IS NOT NULL AND ProcBusCycl = 'COS'"
        Set cRecordset = New ADODB.Recordset
        On Error GoTo Cleanup
        Call cRecordset.Open(SQL, ConnectionString, _
        CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
        CommandTypeEnum.adCmdText)
   Call Sheets("Indicators").Range("E4").CopyFromRecordset (cRecordset)