Excel 2010 WebDav Connection to SharePoint

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:

Please Login or Register  to view this content.

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? I have spent the last 2 days scouring Google and I am starting to lose my mind.

Thanks!

hi,

did u get solution for this issue? if so, please share.

i too face same issue.

Thanks!