Excel 2010 WebDav Connection to SharePoint Errors

[Fern53]

I am in the process of assisting some users in switching from Win XP/Office 2003 -> Win 7/Office 2010 and have hit a roadblock. I have a user that has an excel macro that opens an ADO connection to a SharePoint and grabs a record of the excel files on the SharePoint. The macro works perfectly on XP/2003 and XP/2010 but fails on 7/2010.
They are using this connection line:

davDir.Open "", "URL=http://SharePoint/Directory/URL", _ 
   adModeRead, adFailIfNotExists, adDelayFetchStream
 
Set davFiles = davDir.GetChildren()

Once they have the record they iterate through each Excel file on the SharePoint to open and parse them.

On Win7/2010 I get these errors when it gets to the davDir.Open line:

When they error trap it throws this error:

Object or data matching the name, range, or selection criteria was not found within the scope of this operation.

Without error trapping it throws this error:

Run-time error '-2147217895 (80040e19):Automation error

I can connect to the SharePoint fine via IE or File/Open in Excel 2010. I have tried every variation of the URL I can and I just keep getting the same error.

Does anyone have any ideas? The fact it works on XP/2010 screams Win 7 issue to me, but beyond that I am lost. I have spent the last 2 days scouring Google and I am starting to lose my mind.

Thanks!

 

[SimonKravis]

I've had just this problem and have narrowed it down to making the connection. The following code produces the same error on SP 2007 from Win 7:

Dim Conn As New ADODB.Connection
sURL = "http://sharepointtest2/personal/kraviss/Test docs/ACT Contracts Data" RepositoryRepository"
Conn.Open "URL=" & sURL


I've found WebDav to be most unreliable when uploading files to SP doc libraries and a little less unreliable when reading properties. I use the Frontpage Server extensions to do most uploading. FilesystemObject calls work most of the time to get file and folder properties - the following code fragment will get folder contents. sFolder is the UNC path for the SP doc library

Dim objFSO as New Scripting.filesystemobject
Dim objFolder as Scripting.Folder
Dim colFiles as Scripting.Files
Dim colFolders as Scripting.Folders

sFolder = "\\sharepointtest2\personal\kraviss\Test docs\ACT Contracts Data"
set objFolder = objFSO.GetFolder(sFolder)
set colFiles = objFolder.Files
set colFolders = objFolder.Subfolders

This will need a reference to Microsoft Scripting Runtime scrrun.dll in C:\Windows\SysWOW64