Excel VBA Macro to display SharePoint Content Type Columns

%3CLINGO-SUB%20id%3D%22lingo-sub-3092803%22%20slang%3D%22en-US%22%3EExcel%20VBA%20Macro%20to%20display%20SharePoint%20Content%20Type%20Columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092803%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%20I%20am%20trying%20to%20use%20an%20Excel%20VBA%20macro%20to%20gather%20data%20from%20my%20SharePoint%20content%20type%20columns%20and%20display%20it%20in%20an%20excel%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20using%20the%20CompanyID%20function%20it%20returns%20the%20correct%20result.%20However%2C%20when%20I%20try%20to%20return%20the%20Title%20of%20the%20file%20it%20just%20returns%200.%20Is%20there%20anything%20I%20need%20to%20add%20or%20change%20within%20my%20code%20to%20fix%20this%20issue%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20Title()%0A%20%20Dim%20wb%20As%20Workbook%0A%20%20Set%20wb%20%3D%20ThisWorkbook%0A%20%20For%20Each%20prop%20In%20wb.ContentTypeProperties%0A%20%20%20%20If%20prop.Name%20%3D%20%22Title%22%20Then%0A%20%20%20%20%20%20Title%20%3D%20prop.Value%0A%20%20%20%20End%20If%0A%20%20Next%20prop%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EFunction%20CompanyID()%0A%20%20Dim%20wb%20As%20Workbook%0A%20%20Set%20wb%20%3D%20ThisWorkbook%0A%20%20For%20Each%20prop%20In%20wb.ContentTypeProperties%0A%20%20%20%20If%20prop.Name%20%3D%20%22CompanyID%22%20Then%0A%20%20%20%20%20%20CompanyID%20%3D%20prop.Value%0A%20%20%20%20End%20If%0A%20%20Next%20prop%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20the%202%20cells%20that%20are%20populated.%20I%20have%20used%26nbsp%3B%3DTitle()%20to%20call%20the%20title%20function%20and%20%3DCompanyID()%20to%20call%20the%20CompanyID%20Function.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22EXCEL_BAgf2nTgOR.png%22%20style%3D%22width%3A%20260px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343908i7541BCF9E052902F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22EXCEL_BAgf2nTgOR.png%22%20alt%3D%22EXCEL_BAgf2nTgOR.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20expected%20outcome%20of%20the%20Name%20cell%20should%20be%20Testing%20instead%20of%200.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3092803%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hi there, I am trying to use an Excel VBA macro to gather data from my SharePoint content type columns and display it in an excel spreadsheet.

 

When using the CompanyID function it returns the correct result. However, when I try to return the Title of the file it just returns 0. Is there anything I need to add or change within my code to fix this issue?

 

Function Title()
  Dim wb As Workbook
  Set wb = ThisWorkbook
  For Each prop In wb.ContentTypeProperties
    If prop.Name = "Title" Then
      Title = prop.Value
    End If
  Next prop
End Function
Function CompanyID()
  Dim wb As Workbook
  Set wb = ThisWorkbook
  For Each prop In wb.ContentTypeProperties
    If prop.Name = "CompanyID" Then
      CompanyID = prop.Value
    End If
  Next prop
End Function

 

Here is the 2 cells that are populated. I have used =Title() to call the title function and =CompanyID() to call the CompanyID Function.

EXCEL_BAgf2nTgOR.png

 

 

The expected outcome of the Name cell should be Testing instead of 0.

0 Replies