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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,114,649
Messages
5,549,179
Members
410,903
Latest member
natesreich
Top