Sharepoint QueryTables command missing a Linked Column

Going

New Member
Joined
Nov 5, 2013
Messages
1
Hello,
I am using VBA to call for an export of the SharePoint list I am using into an Excel workbook. The code, seen below, works nearly perfectly but runs into one snag. There is one column in my SharePoint view that is Linked to a different SharePoint List. This column is one of the Checked-boxes type, and gathers the options to be checked from the other list. When the code extracts the data from the SharePoint view I desire, this one column is skipped. I have no authorization to modify the SharePoint configuration, only to read from it. This one column is crucial to my project. I have not seen much information elsewhere on why Linked columns cannot be exported in this fashion. Also, if I use the "Actions>Export to Spreadsheet" function from the sharepoint site, this column IS included. However, the manual demand of that process will not satisfy the requirements of my project. Basically, I am just wondering if anyone knows why the Linked column is not being included in the export, or if there is a way to automatically query SharePoint for this view in a way that will include this linked column. Thanks.


Code:
Public Const MYLIST_VIEWGUID = "{XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX}"
Public Const MYLIST_LISTNAME = "{XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX}"
Public Const MYLIST_LISTWEB = "[URL]http://sharepoint/...[/URL]"
Public Const MYLIST_ROOTFOLDER = "/..."

Sub loadSharePoint(VIEW_GUID As String, LIST_NAME As String, LIST_WEB As String, ROOT_FOLDER As String)
    Dim rList As Range
    Application.ScreenUpdating = False
    
    'reload the SharePoint data
    With sheet.QueryTables.Add(Connection:= _
        "OLEDB;Provider=Microsoft.Office.List.OLEDB.1.0;", Destination:=TODAY.Range(SP_DEST))
        .CommandType = 5
        .CommandText = Array("<LIST><VIEWGUID>" & VIEW_GUID & "</VIEWGUID>" & _
        "<LISTNAME>" & LIST_NAME & "</LISTNAME>" & _
        "<LISTWEB>" & LIST_WEB & "</LISTWEB><LISTSUBWEB></LISTSUBWEB>", _
        "<ROOTFOLDER>" & ROOT_FOLDER & "</ROOTFOLDER></LIST>")
        .Name = "ExternalData_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
        
End Sub
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top