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

  • Question

  • 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

    Thursday, September 20, 2018 1:02 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, September 21, 2018 1:35 AM
  • Hi Ivanhou,


    I’m afraid your needs cannot be achieved. It’s about a security problem. Please refer to this link:

    How to manually pass username & password to get SharePoint 2010 List data via Excel VBA Code

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.




    Friday, September 21, 2018 7:23 AM
    Moderator
  • Hi lvanhou,

    Did you solevd your problem or any updates for this?  If you have any question, or update, please feel free

    to let me know.

    Best Regards,

    Bruce


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Friday, September 28, 2018 2:57 AM
    Moderator
  • I have the same issue and haven't been able to fix it. 
    Wednesday, March 4, 2020 11:34 PM