0

I would like to create a function to get certain data out of a SharePoint list.

This code works more or less:

Public Sub subSharePointList()
    Dim objWorkbook As Workbook
    Dim objWorksheet As Worksheet
    Dim objList As ListObject
    Dim strSite, strList, strView As String

    strSite = "mycompany.sharepoint.com/sites/Department"
    strList = "{XXXXXXXX-4620-44B2-9C99-B5C0A854D5C5}"
    strView = "{XXXXXXXX-05B2-76+A-BC15-C7A68FEC6C30}"

    strSite = "http://" & strSite & "/_vti_bin"
    
    Workbooks.Add
    Set objWorkbook = ActiveWorkbook
    Set objWorksheet = Worksheets.Add

    Set objList = objWorksheet.ListObjects.Add(xlSrcExternal, Array(strSite, strList, strView), False, , Range("A1"))
    varOutput = objWorksheet.Range("A1").CurrentRegion.value
    ActiveWorkbook.Close SaveChanges:=False
End Sub

I'm getting all data in varOutput. However, I'm unable to call it from a function, or make the code in a function format due to objWorksheet.ListObjects I think.

Edit: The function needs to be an UDF/Function that can be called from a sheet.

R.T.
  • 1
  • 2
  • 1
    You certainly may have this code as a function. What you may not is having this code as a function [*called from a sheet*](https://stackoverflow.com/a/3622544/11683). – GSerg Oct 11 '21 at 13:54
  • You mean as a worksheet function? A worksheet function can't create a new workbook, these are the [limitations of custom functions in Excel](https://support.microsoft.com/en-us/topic/description-of-limitations-of-custom-functions-in-excel-f2f0ce5d-8ea5-6ce7-fddc-79d36192b7a1) – Darren Bartrup-Cook Oct 11 '21 at 13:54
  • Is there any workaround to get the result in my UDF? – R.T. Oct 11 '21 at 13:58
  • Looks like there should be - you need to pull the data out of the Sharepoint list without creating the temporary file. – Darren Bartrup-Cook Oct 11 '21 at 14:05
  • Indeed, that's why I'm here. But i can't find any alternative to "objWorksheet.ListObjects.Add" to get my data. – R.T. Oct 11 '21 at 14:08
  • 1
    Add the list once beforehand, on a hidden sheet, then keep refreshing it. That still counts as changing the sheet though, so instead of returning its data as the result of this function, make your other formulas refer to that hidden sheet - at which point, maybe unhide it. The alternative is making the query manually from inside the function, with XMLHTTP or the sort, which you shouldn't really do from such function either. – GSerg Oct 11 '21 at 14:12

0 Answers0