Create a choice field in Sharepoint list using VBA

[pratiksuhasaria]

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 .

    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