Solved

Word mail merge showing 12:00:00AM

Posted on 2014-07-23
4
1,930 Views
Last Modified: 2014-07-26
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.

thanks
 
    ' 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
    With doc.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
'        With .DataSource
'            .FirstRecord = wdDefaultFirstRecord
'            .LastRecord = wdDefaultLastRecord
'        End With
        .Execute Pause:=False
    End With
'
'    Dim docloop As Document
'
'
'
'    For Each docloop In wrdApp.documents
'        myName = docloop

Open in new window



 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
                rs.MoveNext
            Loop
        Else
            Workbooks.Item(2).Sheets("MergeRecords").Cells(myline, c) = "Not Applicable"
        End If
    

Open in new window

0
Comment
Question by:mgmhicks
  • 2
4 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40215195
It's not clear what value is added by using VBA.

I think that we need to see the Mail Merge main document and a sample of the datasource.
0
 

Author Comment

by:mgmhicks
ID: 40215219
thank you for the response.  As it turned out I added code to put a least a space in the field, rather than leave it blank and that worked.  Apparently with the field/column blank it looked at the column as something else, but when I started putting in = space(1), in the column then it started working for all the columns.  Something to keep in mind for the future.
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 40215273
Excel columns don't have a data type as such, but the cells do have NumberFomat property which MaiMerge  would take as a clue.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40219760
12:00:00 AM is usually what you get for a blank Date field.  Sometimes you get the whole date, usually 12/30/1899, which can be disconcerting in a document.  It seems that Excel is somehow processing this cell as a Date value.  There are often data type conversion problems between Access and Excel, especially with dates.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Excel Formula Help 3 35
save excel in the same active file's folder. 8 25
excel table with over 25000 rows. 3 33
Countdown Timer 2 17
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question