Linking Excel ListObject to Sharepoint

Hi All,

Here's the short question: the SharePointURL property of ListObject is ReadOnly. I wanna change it!

The long version: I have a SharePoint site where many months ago I did the "Export to Spreadsheet" command. I've since done lots of work on this file, including code that lets the user refresh the data:

Sheets(RAW_DATA).ListObjects("List1").Refresh

So this is a one way pull-the-data-from-SharePoint process. Works fine, back slaps all around.

Now another division wants to use the same process, but wants to keep their own SharePoint list. Easy enough to give them a duplicate site. Now I want to give them a copy of my Excel file, except that it should pull data from the duplicate site.

I don't want to just "Export to Spreadsheet" again from the new site and copy my Excel changes to that workbook. Too many named ranges, dynamic ranges, etc for that to probably work easily.

So any ideas on how to do the equivalent of changing the SharePointURL property of ListObject to point to the new SharePoint?

Thanks!

Dave