Inserting a shared list from code
 

How do I insert an existing list from a SharePoint site into an Excel worksheet using VBA? I think the following code should work, but when I run it I always get "Can't connect to the server at this time.

Sub InsertList(
Dim wb As Workboo
Set wb = Workbooks.Add(
Dim ws As Workshee
Set ws = wb.Worksheets("Sheet1"
Dim str As Strin
Dim src(2) As Varian
src(0) = "http://wombat.sharepointsite.com
src(1) = "Test List
src(2) = "
ws.ListObjects.Add xlSrcExternal, src, True, xlYes, ws.Range("A1"
End Su

Another thing: When I do this manually (trying to record the action) Excel removes all of the VBA code from the workbook. That's why I added code above to create a new workbook (I thought maybe that was the trick). No luck..

I'm sure my Sharepoint site is working and that the list is accessable -- since I can do this task manually from the list Web page

Thanks!