none
Excel Macro populate SharePoint Form RRS feed

  • Question

  • I am trying to take a macro where when it runs, it can populate a form on a sharepoint site.  So far most of this code works, however, I am having issues with sending information to 3 of the data fields,  two are listed as FieldType="SPFieldUserMulti" and one as FieldType="SPFieldNote".  Any help would be greatly appreciated.        

    Option Explicit

    Sub Create_Ticket()

        Dim Site As Object
        Set Site = New InternetExplorerMedium
        Dim URL As String
        URL = "HTML of my site"

        Site.Navigate URL
        While Site.Busy
        Wend

        Dim oHTMLDoc As Object
        Set oHTMLDoc = Site.Document
        Site.Visible = True


        'Next Line works
        oHTMLDoc.getElementById("ctl00_m_g_6f60cced_f7e9_478a_9b49_6256a35e6213_ctl00_ctl04_ctl01_ctl00_ctl00_ctl04_ctl00_ctl00_TextField").Value = ActiveCell.Value
        'Next line Does not work  - populates an address box (Validates the name entered with the email directory)
        oHTMLDoc.getElementById("ctl00_m_g_6f60cced_f7e9_478a_9b49_6256a35e6213_ctl00_ctl04_ctl02_ctl00_ctl00_ctl04_ctl00_ctl00_UserField_HiddenEntityDisplayText").Value = "John Doe"
        'Next line works
        oHTMLDoc.getElementById("ctl00_m_g_6f60cced_f7e9_478a_9b49_6256a35e6213_ctl00_ctl04_ctl03_ctl00_ctl00_ctl04_ctl00_ctl00_DateTimeField_DateTimeFieldDate").Value = Date
        'Next line does not work - populates an address box (Validates the name entered with the email directory)
        oHTMLDoc.getElementById("ctl00_m_g_6f60cced_f7e9_478a_9b49_6256a35e6213_ctl00_ctl04_ctl04_ctl00_ctl00_ctl04_ctl00_ctl00_UserField").Value = "Help Desk"
        'Next line does not work - populates a multiple line box
        oHTMLDoc.getElementById("ctl00_m_g_6f60cced_f7e9_478a_9b49_6256a35e6213_ctl00_ctl04_ctl05_ctl00_ctl00_ctl04_ctl00_ctl00_TextField").Value = ActiveCell.offset(0,1).value
        'Next line works - populates a drop down box
        oHTMLDoc.getElementById("ctl00_m_g_6f60cced_f7e9_478a_9b49_6256a35e6213_ctl00_ctl04_ctl06_ctl00_ctl00_ctl04_ctl00_DropDownChoice").Value = "Prevent"


        While Site.Busy
        Wend
        Site.Visible = True

    End Sub

    Friday, December 1, 2017 8:03 PM

All replies

  • Hi RS_123456,

    This forum(Excel for Developers) is for development issues related to Excel Object Model and your problem is more related to Visual Basic for Application, so I would move this thread into  Visual Basic for Application.
    Thanks for your understanding.
    Best Regards,
    Terry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 4, 2017 1:43 AM