Microsoft Office Tutorials and References
In Depth Information
Saving Word Form Data to an Excel Spreadsheet
View the Appendix to learn how to store this procedure
in a Standard module (in Word).
Option explicit¶
' * * * * *¶
Const xlWorkbook As String = _¶
"C:\SaveFormData.xls"¶
' * * * * *¶
Sub SendDataToExcel()¶
'Variable declaration¶
Dim objXL As Excel.Application¶
Dim wb As Excel.Workbook¶
Dim rng As Excel.Range¶
Dim rngNewRecord As Excel.Range¶
Dim trackVal As Long¶
Dim rowIndex As Long¶
Dim nrFields As Long¶
Dim fieldName As String¶
Dim counter As Long¶
'Opens the workbook and Excel if necessary¶
Set objXL = New Excel.Application¶
Set wb = objXL.Workbooks.Open(FileName:=xlWorkbook)¶
'Start in the first cell of the table¶
'May be any cell in the worksheet as long¶
'as it is named "Start"¶
Set rng = wb.Sheets(1).Range("Start")¶
'Find the last cell with content¶
trackVal = rng.End(xlDown).Value¶
'Get the number of columns in the table¶
nrFields = rng.End(xlToRight).Column¶
'Get the next empty row¶
If trackVal = 0 Then¶
Set rngNewRecord = rng.Offset(1, 0)¶
Else¶
Set rngNewRecord = rng.End(xlDown).Offset(1, 0)¶
End If¶
'Calculate the next tracking number¶
'for the left column¶
rngNewRecord.Value = trackVal + 1¶
'Get the new record's row number¶
rowIndex = rngNewRecord.Row¶
'Cycle through all the fields in the table¶
For counter = 2 To nrFields¶
'Get the field name (column header)¶
fieldName = _¶
rng.Worksheet.Cells(1, counter).End(xlUp).Value¶
'Insert the data from the matching form field¶
'Ignore fields with no match in the form¶
Cmb
Search JabSto ::




Custom Search