none
Use date from datepicker in Excel userform as input for datepicker in Infopath template RRS feed

  • Question

  • I am creating a userform in excel and want to be able to save the entered data to an infopath template. The code I have written allows me to do this for text fields, but I don't get this to work for the datepicker.

    Code I have so far:

    Private Sub CommandButton3_Click()

        Dim IPApp As InfoPath.Application
        Dim IPDoc As InfoPath.XDocument
        Dim Filename As String
        Filename = Range("'Sheet1'!A1").Text

        Set IPApp = CreateObject("Infopath.Application")
        Set IPDoc = IPApp.XDocuments.Open(Filename)

        With IPDoc
          .DOM.getElementsByTagName("my:Field1").Item(0).Text = TextBox1.Text
          .DOM.getElementsByTagName("my:Field2").Item(0).Text = ComboBox1.Text

        End With

    End Sub

    Friday, March 7, 2014 1:37 PM

Answers

  • I have found the solution, quite logical actually. Line here below works, but in Infopath you have to change the field datatype to "Text (string)".

          .DOM.getElementsByTagName("my:<datepickerfieldname>").Item(0).Text = <datepickercontrolname>.Value

    • Marked as answer by 4GN3 Friday, March 14, 2014 2:21 PM
    Friday, March 14, 2014 2:21 PM

All replies

  • Try converting the date to a formatted string, along the lines of

    .DOM.getElementsByTagName("my:Field3").Item(0).Text = Format(Calendar1.Value, "mm/dd/yyyy")

    Friday, March 7, 2014 1:59 PM
  • That gives me the following error:

    Run-time error '91': Object variable or With block variable not set

    Friday, March 7, 2014 2:13 PM
  • What is the name of the calendar object on your userform?

    What does this show?

    Msgbox Format(<insert name of datepicker (calendar control) here>.Value, "mm/dd/yyyy")


    Friday, March 7, 2014 3:01 PM
  • The name of my calender object is "TimeOfEvent_DTPicker"

    The Msgbox line gave me a messagebox ;-)

    Friday, March 7, 2014 3:09 PM
  • Does this show the selected date?

    Msgbox Format(TimeOfEvent_DTPicker.Value, "mm/dd/yyyy")

    If it does, then try this - though you need to have a field3 in your IPDoc....

    Private Sub CommandButton3_Click()

        Dim IPApp As InfoPath.Application
        Dim IPDoc As InfoPath.XDocument
        Dim Filename As String
        Filename = Range("'Sheet1'!A1").Text

        Set IPApp = CreateObject("Infopath.Application")
        Set IPDoc = IPApp.XDocuments.Open(Filename)

        With IPDoc
          .DOM.getElementsByTagName("my:Field1").Item(0).Text = TextBox1.Text
          .DOM.getElementsByTagName("my:Field2").Item(0).Text = ComboBox1.Text

          .DOM.getElementsByTagName("my:Field3").Item(0).Text = Format(TimeOfEvent_DTPicker.Value, "mm/dd/yyyy")

        End With

    End Sub





    Friday, March 7, 2014 3:27 PM
  • That is what I entered the first time, to be more accurate:

          .DOM.getElementsByTagName("my:TimeOfEvent").Item(0).Text = Format(TimeOfEvent_DTPicker.Value, "mm/dd/yyyy")

    My other lines with the text and comboboxes work, so I don't think it's a naming issue. I aldo made sure that the format "mm/dd/yyyy" is the correct one.

    I am thinking that the '.text' in .DOM.getElementsByTagName("my:TimeOfEvent").Item(0).Text might also not be correct. Both the input as output are from datepickers.

    Friday, March 7, 2014 3:34 PM
  • Have you tried just setting the value

    .DOM.getElementsByTagName("my:TimeOfEvent").Item(0).Value = TimeOfEvent_DTPicker.Value

    Friday, March 7, 2014 3:43 PM
  • '.value' is not a valid option for '.DOM.getElementsByTagName("my:TimeOfEvent").Item(0)'. It gives me the error:

    'Compile error: Method or data member not found'.

    I have tried other options, but most of them give me the error: Run-time error '91': Object variable or With block variable not set

    Friday, March 7, 2014 3:48 PM
  • I'm sorry - I really don't have any experience with xdocuments, so I was just chiming in from a general programming view. 
    Friday, March 7, 2014 4:13 PM
  • Not a problem, thanks for the effort.
    Friday, March 7, 2014 4:16 PM
  • I have found the solution, quite logical actually. Line here below works, but in Infopath you have to change the field datatype to "Text (string)".

          .DOM.getElementsByTagName("my:<datepickerfieldname>").Item(0).Text = <datepickercontrolname>.Value

    • Marked as answer by 4GN3 Friday, March 14, 2014 2:21 PM
    Friday, March 14, 2014 2:21 PM