Set username/password programatically when importing sharepoint 2016 list into excel using vba

[Ivanhou]

Hi,

When a I run this code (excel macro, vba) it prompts for authentication. How can I set username, password in order to automate it? I use MS Excel 2013 and import form Sharepoint 2016.

Thanks,

Ivanhou


Sub importlist()

Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Const LISTNAME As String = "{d0d8c79b-b2eb-4efe-8897-7c2ca2fc4d4d}"
Const VIEWNAME As String = "{484de82c-ef58-48ae-bcc2-83e51a1fe383}"

' The SharePoint server URL pointing to
' the SharePoint list to import into Excel.
strSPServer = "http://mysite/sites/TestSite/_vti_bin"
' Add a new worksheet to the active workbook.
Set objWksheet = Worksheets.Add
' Add a list range to the newly created worksheet
' and populated it with the data from the SharePoint list.
Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _ Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A1"))

End Sub

Continue reading...