I have a spreadsheet that is used as the data file for a word mail merge. This is done in vba. Everything merges over just fine, except 1 column that keeps send over 12:00:00AM even though the spreadsheet data looks something like "Monthy Pet Fee - $20.00"
I have 2 columns like this that are updated by a recordset do while, and the merge field names are nonRent1, nonRent2, nonRent3. nonRent1 comes out great, nonRent2 looks like the same data in nonRent1 but prints as 12:00:00am on the word document. I have deleted the column and added it, I have changed the column format to text, and I don't know why I cant get this to work. I have the code that creates the data and fills the fields, and then also the merge code to show you. Hope someone can help.
' create a new file based on the merge document
Set doc = wrdApp.documents.Add(sPathFileTemplate)
' Hide Word (actually just make sure it doesn't show)
wrdApp.Visible = True
' ' Connect the datasource to the merge document
doc.MailMerge.OpenDataSource Name:=ActiveWorkbook.FullName, LinkToSource:=True, AddToRecentFiles:=False, SQLStatement:="SELECT * FROM `MergeRecords$`"
' Execute the merge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
' With .DataSource
' .FirstRecord = wdDefaultFirstRecord
' .LastRecord = wdDefaultLastRecord
' End With
' Dim docloop As Document
' For Each docloop In wrdApp.documents
' myName = docloop
rs.Open strSQL, con, adOpenStatic
c = 37
If rs.RecordCount > 0 Then
Do Until rs.EOF
Dim myItem As String
myItem = CStr(rs(2)) & Space(1) & "-" & Space(1) & CStr(FormatCurrency(rs("amtofcharge")))
Workbooks.Item(2).Sheets("MergeRecords").Cells(myline, c) = myItem
c = c + 1
Workbooks.Item(2).Sheets("MergeRecords").Cells(myline, c) = "Not Applicable"