Create a choice field in Sharepoint list using VBA

pratiksuhasaria

New Member
Joined
Mar 26, 2019
Messages
24
Hi all,

I have developed a tool which creates a new list in sharepoint when I run a macro. But its takes datatypes of column only as
1) Single line of text
2) Number
3) Date

But I have a column in excel which has a drop down options but after running he marco, when I check my sharepoint list setting the column had datatype as single text of line.
But as I know there is option of choice as datatype in sharepoint which can be used as drop down list.

Please help me with .


VBA Code:
    Dim spList As ListObjects
    Dim newSPlist As ListObject
    Dim sheetName As String
    Dim spPath As String
    Dim spListName As String
       
    sheetName = Sheets("Dashboard").Range("J14").Value  ' name of sheet whose data neds to be created
    spListName = Sheets("Dashboard").Range("J16").Value ' name of the list in the sharepoint
    spPath = Sheets("Dashboard").Range("J18").Value ' path of sharepoint
    Set spList = Sheets(sheetName).ListObjects
    
    lastRow = Sheets(sheetName).Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lastCol = Sheets(sheetName).Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    lastColumn = Split(Cells(1, lastCol).Address, "$")(1)
    
    
    Set newSPlist = spList.Add(xlSrcRange, Sheets(sheetName).Range("A1:" & lastColumn & lastRow), True, xlYes)
    
    newSPlist.Name = spListName
    
    newSPlist.Publish Array(spPath, newSPlist.Name), True

Thanks
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,114,232
Messages
5,546,643
Members
410,752
Latest member
MC01_
Top