Legacy form to Excel Table

[suez00]

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!

[macropod]

See: https://answers.microsoft.com/en-us/...71a59b9?auth=1

[suez00]

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,

[macropod]

Yes, that is the relevant code. As for:

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.

[suez00]

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?

[suez00]

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!