I have created an Excel file that runs a SQL query on several sheets within the same file. This works fine when the file is saved on the user's PC or in the network folder. However if they save it onto Sharepoint, it stops at the point the connection is being opened saying "Invalid internet address". I have tried adapting my code to look the same as the URL which works when pasted into IE but this gives the same error. Can anyone help please?
If Left(strWbPath1, 4) = "http" Then
strWbPath1 = Replace(strWbPath1, " ", Chr(37) & "20")
cn1.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strWbPath1 & ";Extended Properties=""Excel 12.0 Macro;HDR=YES;IMEX=1"";"