Mailmerge from Excel sheet substituting phone number for 0

[welshgasman]

Hi all,

I volunteer for a local Community Car Scheme and we wanted an easy way to keep our passenger list up to date. Previously it was a paper address book.
So I created an Excel workbook, with a sheet for each alpha character and a word mailmerge document to print the addresses for an A5 polypocket.

All seemed to go well with some help on another forum for the SQL for the mailmerge via VBA.

However my controller has notified me that some numbers (atm they appear to be just the mobile numbers, and only a few) come out on the sheet as 0.
I have had a quick look at the excel workbook, and those numbers appear fine, no leading or trailing spaces, and the majority do work, so not just a leading zero issue?

I cannot upload the workbook at present due to the sensitive nature of the data, but was wondering if anyone had come across this before.
It is not any of my conditional fields in the word docuument, I confirmed the value is 0 from the Select via Edit recipient list.

TIA

Here is the code for the mailmerge

Sub Merge_Sheet(pstrSheet As String, pAll As Boolean)
' Sourced from: http://www.vbaexpress.com/forum/showthread.php?70461-Change-Word-mailmerge-source-with-VBA
' Note: this code requires a reference to the Word object model to be set, via Tools|References in the VBE.
On Error GoTo Err_Handler

Application.ScreenUpdating = False
Dim StrMMSrc As String, StrMMDoc As String, StrMMPath As String, StrName As String, strPDFName As String
Dim iLastRow As Integer
Dim wdApp As New Word.Application, wdDoc As Word.Document

StrMMSrc = ThisWorkbook.FullName
StrMMPath = ThisWorkbook.Path & "\"
StrMMDoc = StrMMPath & "GCCS Address Details 7165 MM.docx"
StrName = pstrSheet 'ActiveSheet.Name
Worksheets(StrName).Range("A:J").Columns.AutoFit

' Now get rid of any cells that inflate the used range.
DeleteUnusedRange
'Now sort the sheet for any new entries. Output seems to differ from displayed after sort, so switch back on and off
SortAlpha (StrName)

If Trim(StrName) = "" Then Exit Sub
'Trim Filter column else we get extra records with no values
iLastRow = GetLastRow(StrName, "A") + 1
'ActiveSheet.Range("A" & iLastRow & ":J1000").ClearContents
'ActiveSheet.Range("A" & iLastRow & ":J1000").Delete


wdApp.Visible = True
wdApp.WordBasic.DisableAutoMacros
wdApp.DisplayAlerts = wdAlertsNone
'StrMMDoc = StrMMPath & "MailMergeMainDocument.doc"
Set wdDoc = wdApp.Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
With wdDoc
  With .MailMerge
    .MainDocumentType = wdMailingLabels
    .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
      LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
      "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
      SQLStatement:="SELECT * FROM `" & StrName & "$` "
    .Execute Pause:=False
    .MainDocumentType = wdNotAMergeDocument
  End With
'  .Close SaveChanges:=False
' Document has extra pages with empty labels, unable to find out why.

'Save as PDF file
strPDFName = "GCCS Passengers - " & StrName
With wdApp.ActiveDocument
    .SaveAs Filename:=StrMMPath & strPDFName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
    .Close SaveChanges:=False
End With
wdDoc.Close SaveChanges:=False
'wdApp.Documents("Labels1").ExportAsFixedFormat OutputFileName:= _
'    StrMMPath & strPDFName & ".pdf", _
'    ExportFormat:=wdExportFormatPDF, _
'    OpenAfterExport:=True, _
'    OptimizeFor:=wdExportOptimizeForPrint, _
'    Range:=wdExportAllDocument, _
'    IncludeDocProps:=True, _
'    CreateBookmarks:=wdExportCreateWordBookmarks, _
'    BitmapMissingFonts:=True

End With

wdApp.DisplayAlerts = wdAlertsAll
If Not pAll Then
    MsgBox "Mailmerge document created " & StrMMPath & strPDFName & ".pdf"
End If
wdApp.Quit
ActiveWorkbook.FollowHyperlink StrMMPath & strPDFName & ".pdf"

Err_Resume:
Set wdDoc = Nothing
Set wdApp = Nothing
Application.ScreenUpdating = True

' Now show the results
Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description
    Resume Err_Resume
End Sub

 

[welshgasman]

I should also say, I have checked for hidden characters with Len().

 

[welshgasman]

Have not been able to find the cause, BUT by putting the mobile numbers in the second telephone column, that appears to print them as they are.
Will have to keep an eye on this for a while.

 

[smozgur]

I just removed the solution mark as it doesn't actually explain the problem with the code and data, so it doesn't help future readers. Perhaps, If you could provide a sample data set (without using real data) then helpers might be able to analyze the code with data and find what's wrong with the code. Maybe the code is using the wrong column, or something else.

 

[welshgasman]

I just removed the solution mark as it doesn't actually explain the problem with the code and data, so it doesn't help future readers. Perhaps, If you could provide a sample data set (without using real data) then helpers might be able to analyze the code with data and find what's wrong with the code. Maybe the code is using the wrong column, or something else.

@smozgur
No, definitely using the correct columns, or the other data would not appear.
I am hazarding a guess that as most of the other tel numbers were landline, local and the controller had just entered the last 6 digits of the number, then when Excel came upon an number like 01234 456789 it could not work it out and used 0 in it's place?

I tried linking to Access and that column defaulted to number, when all others were defined as text, when I inspected the table.
However, I would like to get to the bottom of it, so a cleansed xlm file is attached, plus the word doc.

The macro is called Merge_Current_Sheet in Module1
The word documents needs to be in the same folder as the workbook.

OK, how do I upload files on here?

Here is the excel sheet. The two errant entries now have producing 0 in theri address.

Test GCCS Passengers.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Surname	Title	Address 1	Address 2	DataP	Tel 1	Tel 2	Ice	Notes	Filter
2	Haines	Mr & Mrs		Gorseinon	19.6.18	121856				H
3	Halfpenny	Mr & Mrs		Gowerton	21.5.21					H
4	Hall	Mrs		Gowerton		345789				H
5	Hall	Mrs		Penllergaer	30.1.21					H
6	Hamer			Garden Village	26.1.19					H
7	Harris	Mr & Mrs		Garden Village	20.10.21	369874				H
8	Harris	Mr		Loughor	21.2.22					H
9	Harris	Mrs		Gorseinon	3.12.18					H
10	Harris	Mr		Garden Village	5.10.21					H
11	Henderson	Mr & Mrs		Gorseinon	24.6.22					H
12	Heneberry	Mr & Mrs		Gorseinon	21.1.19					H
13	Hill	Mr		Gorseinon	1.10.18					H
14	Hill	Mr		Gowerton	14.11.22					H
15	Hill	Mrs		Loughor	25.5.21					H
16	Holbrook		22 producing 0	Gorseinon	26.2.21	01234 456789				H
17	Holloway	Mrs		Gowerton	20.9.22	569871				H
18	Hopkins	Mrs		Garden Village	27.3.18					H
19	Hopkins	Mrs		Gorseinon	16.7.21					H
20	Hounsell			Gorseinon	18.6.18					H
21	Houston	Mr		Penllergaer	30.9.19					H
22	Howells	Mrs		Gorseinon	23.4.19					H
23	Howells	Mrs		Gorseinon	25.9.18					H
24	Howells			Gorseinon	21.1.19					H
25	Howells	Mrs		Gorseinon	19.9.18					H
26	Howells	Mrs		Grovesend	15.6.18					H
27	Hugh	Mr & Mrs		Loughor	1.12.21					H
28	Hughes		11 producing 0	Loughor	23.4.19	02345 456789				H
29	Hughes			Gorseinon	25.8.18	458973				H
30	Hughes	Mrs		Loughor	14.6.18					H
31	Hughes	Mrs		Loughor	27.2.21					H
32	Hughes	Mrs		Gorseinon	4.3.22					H
33	Hutchings			Gorseinon	25.5.21					H
34
35
36
H

Cell Formulas
Range	Formula
J2:J33	J2	=LEFT(A2)

 

[smozgur]

Thanks for posting the data. Now we have something to work with

First of all, I don't think it is related to the existing code. My opinion is that since Tel 1 starts with a numeric value in the source (first row), mail merge thinks that it is a numeric field, and returns 0 for the text values.

One solution could be using merge field formatting switch in Word. In the document, right-click on the field "Tel 1", and Edit Field.

Click the Field Codes button on the displayed Field dialog and add the following format switch at the end of the field code:

\# "0;;"

Or, alternatively, you can click on Toggle Field Codes and add the formatting switch directly.

Hope this helps.

 

[welshgasman]

Thanks for your reply.
That was my thoughts as well to the cause after I linked the sheet in Access.

I do not think that changing the mailmerge format is going to help at all.
I inspected the data, using Edit Recipient List, and the data had zero in it, so that is all down to Excel?

By using the second column Tel2 for mobiles, that seems to have overcome the issue, for now at least.
Strangely, I did not get an email notification about both your posts, despite having them set?

 

[smozgur]

The formatting switch sounded promising, and actually, it worked for me. But only once, then I think I messed up between document and workbook updates, and stopped restarting once I received your message.

What I am certainly sure of is Excel's "format decision by the first rows" rule, which makes the column format considered as "number". You can test this by simply adding text for the first 8 rows into the source data or leaving them blank which will do the same thing.

Or, better than that, you can select the source column in the worksheet and change the formatting to "Text". Save the workbook, and select it as the source (recipient list) in the Word document. It is important to change the source once you change the formatting, otherwise, Word doesn't change the previously assigned/auto-defined column formatting.

I tested this and I have the following result.

About email notification. Is it possible that you accidentally unwatched this thread? Look at the top-right and see if it says "Watch" or "Unwatch" (it should say "Unwatch).
Second idea: If you came back before the email is sent, then the notification email might be cancelled (since you already saw the reply).

 

[welshgasman]

Thanks smozgur,
We are going to leave the mobile numbers to the second column, as the majority are landlines for the first column.
I will change the column to text though and see what Access thinks of it then.

This thread has Unwatch, and I never got an email about your last post above. I just came here for the Access forum, which I help out in now and again, and spotted the notification. This thread shows Unwatch for me?

Thank you for looking at this and comfirming my suspicions.

Edit: I changed the column on the H sheet to text, and it was picked up correctly by Word, without having to readd the merge field.