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.
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