Is it possible to upload sections of a Excel List to a SharePoint List with VBA?

Question

I'm setting up a new SharePoint List and I want to upload the first four/five columns to the SharePoint (They Match in Name and Contents) and then the 12th Column because it contains the SharePoint's fifth Column. Before you ask I don't I have permissions or authorization to change the location of the 12th column because the document is supposed to have a specific format.

Being new to VBA I'm not sure I understand how to connect to SharePoint sites specifically this list and then upload the excel data. I've manually uploaded excel data before but I want to automate this process so I can just run a macro to upload the items in the sheet (right now I just want them to upload successfully once duplicates can be sorted out later on) I'm not asking for a documented draft of code I could just use some help and pointers from people more familiar with Excel-VBA/SharePoint interactions.

I'm new to VBA so I whipped up opening the workbook (but not the sheet) that contains my data (I chose to use a open file method because I won't be the only one using this macro so I decided to allow people to navigate to their directories.

Sub UploadUntimed()
    Dim my_FileName As Variant
    my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")
    If my_FileName <> False Then
        Workbooks.Open Filename:=my_FileName
    End If
End Sub

ASH ASH · Accepted Answer · 2019-06-25T12:51:54

Please try this.

Private Sub CreateList()
    Dim folder As folder
    Dim f As File
    Dim fs As New FileSystemObject
    Dim RowCtr As Integer
    RowCtr = 1
    Set folder = fs.GetFolder("http://your_url_here/") '<=Variable Location
    For Each f In folder.Files
       Cells(RowCtr, 1).Value = f.Name
       RowCtr = RowCtr + 1
    Next f
End Sub

Also, try this.

Sub UpdateSpecificCells()

'If nobody has the file checked out
If Workbooks.CanCheckOut("http://your_url_here/ExcelList.xlsb") = True Then
Application.DisplayAlerts = False

'Open the file on the SharePoint server
Workbooks.Open Filename:="http://your_url_here/ExcelList.xlsb", UpdateLinks:=xlUpdateLinksNever


ActiveSheet.Cells(2, 7).Value = 100
ActiveSheet.Cells(3, 7).Value = 200
ActiveSheet.Cells(4, 7).Value = 300


'Close the workbook
Workbooks("ExcelList.xlsb").Save
Workbooks("ExcelList.xlsb").Close

End If
End Sub

Is it possible to upload sections of a Excel List to a SharePoint List with VBA?

1 Answers