Error 1004 from a .CommandType = 5 line for data import

[webkilla]

Greetings wise ones

I have been burdened with glorious purpose by my boss, to create an excell file that auto-imports a big ol' list of tables from our sharepoint database, every time the document is opened - mainly to use it as an auto-generating list which is then to be saved into a new file so the original remains blank.

Problem is that the code I've generated via the macro recorder doesn't seem to be working - and this is way above my usual level of just using excell like a really handy mass calculator and graph maker. I know how to use the macro recorder, but I don't understand everything it cranks out, so debugging that is an issue for me.

My code appears as follows: (with the line throwing off the 1004 highlighted)

I should note that currently the code is set up in a macro, tied to a button, so I don't have to open and close the workbook constantly to test things.

Sub Import_List()
'
' Import_List Macro
' This code automatically imports the tool list in the right view
''
    With Sheets("Sheet1").ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="""";ApplicationName=Excel;Version=12.0.0.0" _
        , Destination:=Range("$A$1")).QueryTable
[B]        .CommandType = 5 'This is the line that throws off the error. I have no clue why. From what I can tell its a pointer to list data. [/B]
        .CommandText = Array( _
        "<list><viewguid>{75AB5376-AC5F-444A-A7AE-D984214EA11E}</viewguid><listname>{A6479E9F-EA76-424D-B9BA-F8C2845F3765}        , _
        "LISTNAME><listweb>http://teamsite.connect.coloplast.com/sites/Engineering/injection_moulding/Equipment/injectionmou" _
        , _
        "lding/_vti_bin</listweb><listsubweb></listsubweb><rootfolder>/sites/Engineering/injection_moulding/Equipment/inject" _
        , "ionmoulding/Lists/Tool Overview</rootfolder></listname>")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = "C:\Users\DKALE\Desktop\owssvr.odc"
        .ListObject.DisplayName = "Table_owssvr"
        .Refresh BackgroundQuery:=False
    End With
End Sub

As mentioned previously, then I used the macro recorder to produce the code - and when I use that, it also imports the data just fine. Its when I copy that code onto a new document with the same source connection file set up in advance, basically a file identical to the one I generate the code in, and trigger the import via the button that it wont work.

It also gives the same error if I just delete the imported tables and push the button.

Does anyone have any clues what might be wrong, or how I can make this work?</list>