Consulting

Results 1 to 6 of 6

Thread: Legacy form to Excel Table

  1. #1
    VBAX Newbie
    Joined
    Nov 2017
    Posts
    5
    Location

    Legacy form to Excel Table

    Hello everyone. I am on a mission to transfer some information from a word form to an excel document.
    I've completed my code but wish to polish it up to be a little more user friendly.
    I want to add check-box form fields and drop-down form fields instead of just having text form fields.

    I'm having difficulty figuring out how i could go about this with check boxes.

    Sub TransferToExcel()'Transfer a single record from the form fields to an Excel workbook.
      Dim doc As Document
      Dim strCompanyName As String
      Dim strPhone As String
      Dim strTransferInOut As String
      Dim strInitialCall As String
      'Dim strReferralSource As String
      '
      '
      '
      Dim strComments As String
      Dim strSQL As String
      Dim cnn As ADODB.Connection
      'Get data.
      Set doc = ThisDocument
      On Error GoTo ErrHandler
      strCompanyName = Chr(39) & doc.FormFields("txtCompanyName").Result & Chr(39)
      strPhone = Chr(39) & doc.FormFields("txtPhone").Result & Chr(39)
      strTransferInOut = Chr(39) & doc.FormFields("txtTransferInOut").Result & Chr(39)
      strInitialCall = Chr(39) & doc.FormFields("txtInitialCall").Result & Chr(39)
      strComments = Chr(39) & doc.FormFields("txtComments").Result & Chr(39)
      'Define sql string used to insert each record in the destination workbook.
      'Don't omit the $ in the sheet identifier.
      strSQL = "INSERT INTO [PhoneList$]" _
        & " (CompanyName, Phone, TransferInOut, InitialCall, Comments)" _
        & " VALUES (" _
        & strCompanyName & ", " _
        & strPhone & ", " _
        & strTransferInOut & ", " _
        & strInitialCall & ", " _
        & strComments _
        & ")"   
    '  
    '
      Debug.Print strSQL
      'Define connection string and open connection to destination workbook file.
      Set cnn = New ADODB.Connection
      With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=H:\ABC\SalesTest.xlsx;" & _
          "Extended Properties=Excel 8.0;"
        .Open
        'Transfer data.
        .Execute strSQL
      End With
      Set doc = Nothing
      Set cnn = Nothing
      Exit Sub
    ErrHandler:
      MsgBox Err.Number & ": " & Err.Description, _
        vbOKOnly, "Error"
      On Error GoTo 0
      On Error Resume Next
      cnn.Close
      Set doc = Nothing
      Set cnn = Nothing
    End Sub
    Does anyone have any suggestions on how to incorporate check boxes into the code and onto the excel cell? I'd like a check-box on the form to have two options: Yes/No
    If yes is selected, "yes" will print in excel.

    any help is appreciated.

    thank you!

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,868
    Location
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    VBAX Newbie
    Joined
    Nov 2017
    Posts
    5
    Location
    Hello Paul,

    Thank you for the response!
    Are you referring to the " Dim FmFld As Word.FormField"
    and "
    For Each FmFld In .FormFields
    j = j + 1
    With FmFld
    Select Case .Type
    Case Is = wdFieldFormCheckBox"

    section of that code?


    Is there a certain dimension for checkboxes/dropdown fields that must be used? Also,and how can I extract the selected values?

    Thank you,

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,868
    Location
    Yes, that is the relevant code. As for:
    Quote Originally Posted by suez00 View Post
    Is there a certain dimension for checkboxes/dropdown fields that must be used? Also,and how can I extract the selected values?
    All you're extracting is the checkbox true/false state and the dropdown's displayed contents, not the actual fields, so dimension issues are irrelevant. Had you run the code in the link, that'd have been quite obvious.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    VBAX Newbie
    Joined
    Nov 2017
    Posts
    5
    Location
    I disabled the auto macro, enabled the Microsoft word 15.0 object library,ran the code, and selected the folder with the word form.
    Unfortunately, after some hesitation, it does not give me anything in my excel sheet.

    Am I missing something?

  6. #6
    VBAX Newbie
    Joined
    Nov 2017
    Posts
    5
    Location
    After messing around with different word files i think i found the reason.
    It looks like the macro will only read Microsoft word 97-2003 documents. It will not read .Docx or Docm extensions.

    Edit: Its in the Code.... just changed the following line to what I needed:
    strFile = Dir(strFolder & "\*.doc", vbNormal)
    Thanks so much Paul!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •