Create SaveAs filename in VBA with text from the Word document

[Zand02]

Let me explain:
I made a macro that splits a Word mergefile into seperate files and saves these as individual pdf files.
The Word document is merged from as Excel file with names, addresses and some calculated numbers.

ActiveDocument.SaveAs FileName:="Individual_letter_2015_" & DocNum & ".pdf", FileFormat:= _
wdFormatPDF

With a counter the filename is numbered...1, 2, 3 etc.
Individual_letter_2015_1.pdf etc.

This works perfectly, but I want to identify the file based on the content, so a name (or initials) of the addressed person.

I would like to selected a piece of text from a fixed position in the Word document.
Store that in a variable, say "DocNameID"
And then save like this with DocNameID in stead of DocNum:
ActiveDocument.SaveAs FileName:="Individual_letter_2015_" & DocNameID & ".pdf", FileFormat:= _
wdFormatPDF

Simplest: In the merge I can insert a three letter initial of the person and make that white in the Word document so it is not printed.
Better but probably more difficult: Selected the persons name, add "_" for the spaces (e.g. Jan_van_Amsterdam)... but that is a random number of characters.

Can anyone give a hint of how to VBA select the name and store that in a variable (postion of text fixed on page, Row X, Character 0...end of text)

Thanks!

Next step is to send the letters via a merged Outlook action, with the letters (pdf) as individual attachments.
I found a YouTube on how to do that... but to test that I first need the individually recognisable named pdf's...

[gmaxey]

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
'You said you wanted to get data from a "fixed" postion in the document.
'Why not the mergefield "Name"
Dim oFld As Field, strDocNameID As String
For Each oFld In ActiveDocument.Fields
If oFld.Type = wdFieldMergeField Then
If Mid(oFld.Code, 14, 4) = "Name" Then
strDocNameID = oFld.Result
strDocNameID = Replace(strDocNameID, " ", "_")
Exit For
End If
End If
Next
ActiveDocument.SaveAs FileName:="Individual_letter_2015_" & strDocNameID & ".pdf", FileFormat:= _
wdFormatPDF
lbl_Exit:
Exit Sub

End Sub

[Zand02]

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
'You said you wanted to get data from a "fixed" postion in the document.
'Why not the mergefield "Name"
Dim oFld As Field, strDocNameID As String
For Each oFld In ActiveDocument.Fields
If oFld.Type = wdFieldMergeField Then
If Mid(oFld.Code, 14, 4) = "Name" Then
strDocNameID = oFld.Result
strDocNameID = Replace(strDocNameID, " ", "_")
Exit For
End If
End If
Next
ActiveDocument.SaveAs FileName:="Individual_letter_2015_" & strDocNameID & ".pdf", FileFormat:= _
wdFormatPDF
lbl_Exit:
Exit Sub

End Sub



Hello Greg,

I thought of using the Mergefield "Name" also (and your code is nice) but in the merged document (new Word file with all the merged letters) the Mergefield are not there anymore... all have been replaced by the actual data values. ALT-F9 shows nothing.

Or is it...?

I am spliting this merged file with all the letters and need to extract something to create the filename from each letter (single page in the document).

Greetz, Zand

[gmayor]

The obvious solution is to split the merge while you do the merge http://www.gmayor.com/individual_merge_letters.htm and http://www.gmayor.com/MergeAndSplit.htm will do that (the latter more or less as you envisaged) and the first link has code which shows how to do it yourself. Both add-ins will create unique filenames, regardless of any duplication in the data, the former having a little more control over the layout of the filename, using fields and text.

[Zand02]

The obvious solution is to split the merge while you do the merge [censured] and [censured] will do that (the latter more or less as you envisaged) and the first link has code which shows how to do it yourself. Both add-ins will create unique filenames, regardless of any duplication in the data, the former having a little more control over the layout of the filename, using fields and text.

Hello Gmayor,

I understand what you mean and I found your solution... I think the only one on the web that does what so many people want.
Very neat but I do not want to use a ready made plug-in, I want to learn and build a solution myself. I always look for the simplest solution possible, as less code as possible, as less bells and whistles possible...

Good luck with you plug-in but I will skip this one for now...

Greetings, Zand02

[Zand02]

I added code to select the name from the recipient. That works, I can paste it in the document (just for testing).
How can I store the content of that selection in a variable so I can build the filename?

' Goto Home, move down to name and select name by select END minus 1 character.
Selection.HomeKey Unit:=wdStory
Selection.MoveDown Unit:=wdLine, Count:=2
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
*** DocName=selection.something... ***
*** Can I FindAndReplace the spaces in the name (that is in the selection or variable by now) with a "_" so that Jan van Putten becomes Jan_van_Putten *** (I do not want spaces in the filename)

Any suggestions?

[gmayor]

If you want simple then investigate ranges. The web page that features the add-in you dismissed so readily, also includes example code.
Declare a variable
DIM strFilename as String
Having selected the text add the lines

strFilename = Replace(Selection.Range.Text, Chr(32), Chr(95))
strFilename = "C:\Path\" & strFilename & ".docx"
to replace the spaces.
Note that your code does not include any handling for duplicated file names. Already it is looking less simple :(

[Zand02]

If you want simple then investigate ranges. The web page that features the add-in you dismissed so readily, also includes example code.
Declare a variable
DIM strFilename as String
Having selected the text add the lines

strFilename = Replace(Selection.Range.Text, Chr(32), Chr(95))
strFilename = "C:\Path\" & strFilename & ".docx"
to replace the spaces.
Note that your code does not include any handling for duplicated file names. Already it is looking less simple :(


Hello gmayor (Graham),

I have seen you got lots and lots of code there... I appreciate that you still want to help after my comment.

Is it really that simple... ?!
I will add this and see if I can get it to work.

Good remark by the way about the duplicate filenames..
You could end up with a "allready exists" but I do not have double names in de datafile, so that will not be an issue.

Thanks!

Greetings, Zand

[Zand02]

Hello gmayor (Graham),

I am sorry to say that the marco halts and gives an error on this line:
strFilename = Replace(Selection.Range.Text, Chr(32), Chr(95))

The variable is declarerd:
DIM strFilename As String


Am I missing ""'s maybe because this is an String variable?!

Any suggestions?

Greetings, Pim

[gmayor]

You are not missing anything.
Reboot the PC then select a short piece of text and run the following macro

Sub Macro1()
Dim strFilename As String
strFilename = Replace(Selection.Range.Text, Chr(32), Chr(95))
MsgBox strFilename
End Sub
Does it crash at the line? If not run your macro again. Does that crash? If so, post your code.

[Zand02]

You are not missing anything.
Reboot the PC then select a short piece of text and run the following macro

Sub Macro1()
Dim strFilename As String
strFilename = Replace(Selection.Range.Text, Chr(32), Chr(95))
MsgBox strFilename
End Sub
Does it crash at the line? If not run your macro again. Does that crash? If so, post your code.


Hello gmayor,

I took the lines line apart and made it this:
DocNameVar = Replace(Selection.Range.Text, Chr(32), Chr(95))
into this:
DocNameVar = Selection.Range.Text
DocNameVar = Replace(DocNameVar, Chr(32), Chr(95))

Now it works,
I have my filename with the personsname in it, with "_" on the spaces.

I will try the above check to see what happens...

Greetins, Zand

[Zand02]

Hello gmayor,

I took the lines line apart and made it this:
DocNameVar = Replace(Selection.Range.Text, Chr(32), Chr(95))
into this:
DocNameVar = Selection.Range.Text
DocNameVar = Replace(DocNameVar, Chr(32), Chr(95))

Now it works,
I have my filename with the personsname in it, with "_" on the spaces.

I will try the above check to see what happens...

Greetins, Zand


Hello gmayor,

Retested and now both variants do work!
Also in the complete code.
Maybe an overlooked typing error on my side...
Thanks for the effort!


Sub TEST()
Dim DocNameVar As String
'AB CD selected for test
DocNameVar = Selection.Range.Text
DocNameVar = Replace(DocNameVar, Chr(32), Chr(95))
MsgBox DocNameVar
End Sub

Sub TEST()
Dim DocNameVar As String
'AB CD selected for test
DocNameVar = Replace(Selection.Range.Text, Chr(32), Chr(95))
MsgBox DocNameVar
End Sub

Greetings, Zand

SOLVED