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.
Code:
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!