[Bernie]

I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will be a merged
field. There will be approx 20 lines to be merged into various places in the
document.
How do you set up the word doc to receive these fields?

[Graham Mayor]

I assume that the list will be merged into various places in the same
document, rather than one place in 20 different documents?
That being the case, you will need first to convert the list to a merge data
format.

This is probably simplest to do in Excel. Open the text file in Excel then
transpose the list so that the entries are all in one row. insert a header
row and name the columns A, B, C etc will work.

Save the file as an Excel document and use that as a merge data source for a
letter merge. Insert the 20 fields where you want them. Merge to a new
document.

If the list is to be merged into the same place in 20 documents, open the
list in Word convert the text to a one column table. Add a row at the top
and give the column a name. Save as a document and use that as a merge data
source.

The principles of mail merge are covered at
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will be a
merged field. There will be approx 20 lines to be merged into various
places in the document.
How do you set up the word doc to receive these fields?

[Bernie]

Hi Graham,
Your first paragraph is correct. Can you tell me what a "merge data format"
looks like?
Then I could add that to my ascii file. I need to automate this process so
going thru Excel is not a choice.
Also, can you give me the steps for specifying how to create the merge field
into the Word doc and how do I specify to Word, to get the data from my ascii
file.
Thanks in advance for your help.

Bernie

"Graham Mayor" wrote:

I assume that the list will be merged into various places in the same
document, rather than one place in 20 different documents?
That being the case, you will need first to convert the list to a merge data
format.

This is probably simplest to do in Excel. Open the text file in Excel then
transpose the list so that the entries are all in one row. insert a header
row and name the columns A, B, C etc will work.

Save the file as an Excel document and use that as a merge data source for a
letter merge. Insert the 20 fields where you want them. Merge to a new
document.

If the list is to be merged into the same place in 20 documents, open the
list in Word convert the text to a one column table. Add a row at the top
and give the column a name. Save as a document and use that as a merge data
source.

The principles of mail merge are covered at
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will be a
merged field. There will be approx 20 lines to be merged into various
places in the document.
How do you set up the word doc to receive these fields?

[Suzanne S. Barnhill]

Find more information on mail merge data sources at
http://word.mvps.org/FAQs/MailMerge/...DataSource.htm. Note that
comma-delimited text can be used (section 5 of that article).

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
http://word.mvps.org

"Bernie" wrote in message
...
Hi Graham,
Your first paragraph is correct. Can you tell me what a "merge data
format"
looks like?
Then I could add that to my ascii file. I need to automate this process so
going thru Excel is not a choice.
Also, can you give me the steps for specifying how to create the merge
field
into the Word doc and how do I specify to Word, to get the data from my
ascii
file.
Thanks in advance for your help.

Bernie

"Graham Mayor" wrote:

I assume that the list will be merged into various places in the same
document, rather than one place in 20 different documents?
That being the case, you will need first to convert the list to a merge
data
format.

This is probably simplest to do in Excel. Open the text file in Excel
then
transpose the list so that the entries are all in one row. insert a
header
row and name the columns A, B, C etc will work.

Save the file as an Excel document and use that as a merge data source
for a
letter merge. Insert the 20 fields where you want them. Merge to a new
document.

If the list is to be merged into the same place in 20 documents, open the
list in Word convert the text to a one column table. Add a row at the top
and give the column a name. Save as a document and use that as a merge
data
source.

The principles of mail merge are covered at
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will be a
merged field. There will be approx 20 lines to be merged into various
places in the document.
How do you set up the word doc to receive these fields?

[Graham Mayor]

My second paragraph explains what a merge data format looks like - the
simplest format is a table with a header row, but it could be a comma
delimited file with a header row. My last paragraph has links to tutorials
for the mail merge process.

As we don't know what Word version you have and as this is an ASCII file the
following macro contains more code than strictly necessary, but it should
create a data file in Word Doc or Docx format from your list without
altering the ASCII file. You can then use that data file in connection with
a mail merge. The fields are named Field_1 to Field_n where n is the number
of rows in your list and reflect the order in which they appear in the list.
Insert the fields in your document where you want the data to appear.

Sub CreateDataFormat()
Dim lNum As Long
Dim i As Long
Dim fNum As Long
lNum = 0
fNum = 0
With Selection
.HomeKey wdStory
.Find.Execute findText:="^13^13", _
ReplaceWith:="", _
Forward:=True, _
Replace:=wdReplaceAll
For i = 1 To ActiveDocument.Characters.Count
If ActiveDocument.Characters(i) = Chr(13) Then
lNum = lNum + 1
End If
Next i
.Find.Execute findText:="^13", _
ReplaceWith:=", ", _
Forward:=True, _
Replace:=wdReplaceAll
.EndKey wdStory
.Find.Execute findText:=", ", _
ReplaceWith:="", _
Forward:=False, _
Replace:=wdReplaceOne
.HomeKey wdStory
For i = 1 To lNum
fNum = fNum + 1
.TypeText "Field_" & fNum
If i lNum Then .TypeText ", "
Next i
.TypeParagraph
End With
ActiveDocument.Select
WordBasic.TextToTable ConvertFrom:=2, NumColumns:=lNum, NumRows:=2, _
InitialColWidth:=wdAutoPosition, Format:=0, Apply:=1184, AutoFit:=0,
_
SetDefault:=0, Word8:=0, Style:="Table Grid"
strDocName = Split(ActiveDocument.FullName, ".")
If Application.version = 12 Then
ActiveDocument.SaveAs _
FileName:=strDocName(0) & ".docx", _
FileFormat:=16
Else
ActiveDocument.SaveAs _
FileName:=strDocName(0) & ".doc", _
FileFormat:=0
End If
End Sub

http://www.gmayor.com/installing_macro.htm


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
Hi Graham,
Your first paragraph is correct. Can you tell me what a "merge data
format" looks like?
Then I could add that to my ascii file. I need to automate this
process so going thru Excel is not a choice.
Also, can you give me the steps for specifying how to create the
merge field into the Word doc and how do I specify to Word, to get
the data from my ascii file.
Thanks in advance for your help.

Bernie

"Graham Mayor" wrote:

I assume that the list will be merged into various places in the same
document, rather than one place in 20 different documents?
That being the case, you will need first to convert the list to a
merge data format.

This is probably simplest to do in Excel. Open the text file in
Excel then transpose the list so that the entries are all in one
row. insert a header row and name the columns A, B, C etc will work.

Save the file as an Excel document and use that as a merge data
source for a letter merge. Insert the 20 fields where you want them.
Merge to a new document.

If the list is to be merged into the same place in 20 documents,
open the list in Word convert the text to a one column table. Add a
row at the top and give the column a name. Save as a document and
use that as a merge data source.

The principles of mail merge are covered at
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will be
a merged field. There will be approx 20 lines to be merged into
various places in the document.
How do you set up the word doc to receive these fields?

[Peter Jamieson]

If you have to resort to using a macro, the following approach might be
simpler:

Run the following macro to transfer each line into a "document variable"
so that variable "line1" contains the first line from your file,
variable "line2" contains the second line, and so on.

Then you can insert (say) the 3rd line using a field like

{ DOCVARIABLE line3 }

Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
ActiveDocument.Variables.Add "line" & iCount, strLine
Wend
Close #1
End Sub

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Bernie wrote:
Hi Graham,
Your first paragraph is correct. Can you tell me what a "merge data format"
looks like?
Then I could add that to my ascii file. I need to automate this process so
going thru Excel is not a choice.
Also, can you give me the steps for specifying how to create the merge field
into the Word doc and how do I specify to Word, to get the data from my ascii
file.
Thanks in advance for your help.

Bernie

"Graham Mayor" wrote:

I assume that the list will be merged into various places in the same
document, rather than one place in 20 different documents?
That being the case, you will need first to convert the list to a merge data
format.

This is probably simplest to do in Excel. Open the text file in Excel then
transpose the list so that the entries are all in one row. insert a header
row and name the columns A, B, C etc will work.

Save the file as an Excel document and use that as a merge data source for a
letter merge. Insert the 20 fields where you want them. Merge to a new
document.

If the list is to be merged into the same place in 20 documents, open the
list in Word convert the text to a one column table. Add a row at the top
and give the column a name. Save as a document and use that as a merge data
source.

The principles of mail merge are covered at
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will be a
merged field. There will be approx 20 lines to be merged into various
places in the document.
How do you set up the word doc to receive these fields?

[Graham Mayor]

Good thinking - That has the makings of a plan

I have taken the liberty of making a couple of minor changes to cover the
issue of one or more empty lines in the text file and to remove all relevant
docvariables in the document that may have been created by a previous longer
file, for I have the feeling that this is going to be a repeat requirement.
I have also added a message box at the end so the user knows how many
variables are present. If the file is always going to be of the same length,
this message box can be omitted.

Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
ActiveDocument.Variables.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
MsgBox "Variables line1 to line" & iCount & " created"
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Peter Jamieson wrote:
If you have to resort to using a macro, the following approach might
be simpler:

Run the following macro to transfer each line into a "document
variable" so that variable "line1" contains the first line from your
file, variable "line2" contains the second line, and so on.

Then you can insert (say) the 3rd line using a field like

{ DOCVARIABLE line3 }

Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
ActiveDocument.Variables.Add "line" & iCount, strLine
Wend
Close #1
End Sub

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Bernie wrote:
Hi Graham,
Your first paragraph is correct. Can you tell me what a "merge data
format" looks like?
Then I could add that to my ascii file. I need to automate this
process so going thru Excel is not a choice.
Also, can you give me the steps for specifying how to create the
merge field into the Word doc and how do I specify to Word, to get
the data from my ascii file.
Thanks in advance for your help.

Bernie

"Graham Mayor" wrote:

I assume that the list will be merged into various places in the
same document, rather than one place in 20 different documents?
That being the case, you will need first to convert the list to a
merge data format.

This is probably simplest to do in Excel. Open the text file in
Excel then transpose the list so that the entries are all in one
row. insert a header row and name the columns A, B, C etc will work.

Save the file as an Excel document and use that as a merge data
source for a letter merge. Insert the 20 fields where you want
them. Merge to a new document.

If the list is to be merged into the same place in 20 documents,
open the list in Word convert the text to a one column table. Add a
row at the top and give the column a name. Save as a document and
use that as a merge data source.

The principles of mail merge are covered at
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will
be a merged field. There will be approx 20 lines to be merged into
various places in the document.
How do you set up the word doc to receive these fields?

[Graham Mayor]

Having far too much time on my hands today and having created all those
variables in the document, a quick and easy means of inserting the
associated fields in the document would not be a bad idea so the following
macro will do that. Each time it is run it inserts the next numbered
docvariable field at the cursor (so run it from a toolbar button of keyboard
shortcut). It requires a further mod to Peter's macro, which is included
that counts the available fields and prevents the user from adding more
docvariable fields than he has docvariables. Re-run that macro if necessary
to add the extra variable that stores the count.

Sub InsertLineVariable()
Dim oVars As Variables
Dim vVar As Variant
Dim aNum As Integer
Dim bNum As Integer
Dim bExists As Boolean
Dim varExists As Boolean
Set oVars = ActiveDocument.Variables
bExists = False
varExists = False
For Each vVar In oVars
If vVar.name = "numVars" Then
varExists = True
Exit For
End If
Next vVar
For Each vVar In oVars
If vVar.name = "varCount" Then
bExists = True
Exit For
End If
Next vVar
If varExists = False Then
MsgBox "There are no variables in the document" & _
" associated with this macro", vbCritical, "Error"
Exit Sub
End If
If bExists = False Then
oVars("varCount").Value = 0
End If
oVars("varCount").Value = oVars("varCount").Value + 1
aNum = oVars("varCount").Value
bNum = oVars("numVars").Value
If aNum bNum Then
MsgBox "There are no more variables to insert", _
vbInformation, "Insert Variables"
Exit Sub
End If
Selection.Fields.Add Selection.Range, _
wdFieldDocVariable, "line" & _
oVars("varCount").Value, False
End Sub



Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim bExists As Boolean
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
bExists = False
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "D:\My Documents\Test\Versions\filename.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
oVars.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
'MsgBox "Variables line1 to line" & iCount & " created"
For Each vVar In oVars
If vVar.name = "numVars" Then
bExists = True
oVars("numVars").Value = iCount
Exit For
End If
Next vVar
If bExists = False Then
oVars.Add "numVars", iCount
End If
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Graham Mayor wrote:
Good thinking - That has the makings of a plan

I have taken the liberty of making a couple of minor changes to cover
the issue of one or more empty lines in the text file and to remove
all relevant docvariables in the document that may have been created
by a previous longer file, for I have the feeling that this is going
to be a repeat requirement. I have also added a message box at the
end so the user knows how many variables are present. If the file is
always going to be of the same length, this message box can be
omitted.
Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
ActiveDocument.Variables.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
MsgBox "Variables line1 to line" & iCount & " created"
End Sub


Peter Jamieson wrote:
If you have to resort to using a macro, the following approach might
be simpler:

Run the following macro to transfer each line into a "document
variable" so that variable "line1" contains the first line from your
file, variable "line2" contains the second line, and so on.

Then you can insert (say) the 3rd line using a field like

{ DOCVARIABLE line3 }

Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
ActiveDocument.Variables.Add "line" & iCount, strLine
Wend
Close #1
End Sub

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Bernie wrote:
Hi Graham,
Your first paragraph is correct. Can you tell me what a "merge data
format" looks like?
Then I could add that to my ascii file. I need to automate this
process so going thru Excel is not a choice.
Also, can you give me the steps for specifying how to create the
merge field into the Word doc and how do I specify to Word, to get
the data from my ascii file.
Thanks in advance for your help.

Bernie

"Graham Mayor" wrote:

I assume that the list will be merged into various places in the
same document, rather than one place in 20 different documents?
That being the case, you will need first to convert the list to a
merge data format.

This is probably simplest to do in Excel. Open the text file in
Excel then transpose the list so that the entries are all in one
row. insert a header row and name the columns A, B, C etc will
work. Save the file as an Excel document and use that as a merge data
source for a letter merge. Insert the 20 fields where you want
them. Merge to a new document.

If the list is to be merged into the same place in 20 documents,
open the list in Word convert the text to a one column table. Add a
row at the top and give the column a name. Save as a document and
use that as a merge data source.

The principles of mail merge are covered at
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will
be a merged field. There will be approx 20 lines to be merged into
various places in the document.
How do you set up the word doc to receive these fields?

[Bernie]

Thank you Peter and Graham,
What kind of macros are these? How do I run them?
From my Database I can "Launch MyWordDoc.doc" which will bring up the word
document. Then what do I do to run the macro you have most generously
provided?
P.S. the database I am using is R:Base version 7.6 I am using SQL commands.

"Graham Mayor" wrote:

Having far too much time on my hands today and having created all those
variables in the document, a quick and easy means of inserting the
associated fields in the document would not be a bad idea so the following
macro will do that. Each time it is run it inserts the next numbered
docvariable field at the cursor (so run it from a toolbar button of keyboard
shortcut). It requires a further mod to Peter's macro, which is included
that counts the available fields and prevents the user from adding more
docvariable fields than he has docvariables. Re-run that macro if necessary
to add the extra variable that stores the count.

Sub InsertLineVariable()
Dim oVars As Variables
Dim vVar As Variant
Dim aNum As Integer
Dim bNum As Integer
Dim bExists As Boolean
Dim varExists As Boolean
Set oVars = ActiveDocument.Variables
bExists = False
varExists = False
For Each vVar In oVars
If vVar.name = "numVars" Then
varExists = True
Exit For
End If
Next vVar
For Each vVar In oVars
If vVar.name = "varCount" Then
bExists = True
Exit For
End If
Next vVar
If varExists = False Then
MsgBox "There are no variables in the document" & _
" associated with this macro", vbCritical, "Error"
Exit Sub
End If
If bExists = False Then
oVars("varCount").Value = 0
End If
oVars("varCount").Value = oVars("varCount").Value + 1
aNum = oVars("varCount").Value
bNum = oVars("numVars").Value
If aNum bNum Then
MsgBox "There are no more variables to insert", _
vbInformation, "Insert Variables"
Exit Sub
End If
Selection.Fields.Add Selection.Range, _
wdFieldDocVariable, "line" & _
oVars("varCount").Value, False
End Sub



Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim bExists As Boolean
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
bExists = False
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "D:\My Documents\Test\Versions\filename.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
oVars.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
'MsgBox "Variables line1 to line" & iCount & " created"
For Each vVar In oVars
If vVar.name = "numVars" Then
bExists = True
oVars("numVars").Value = iCount
Exit For
End If
Next vVar
If bExists = False Then
oVars.Add "numVars", iCount
End If
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Graham Mayor wrote:
Good thinking - That has the makings of a plan

I have taken the liberty of making a couple of minor changes to cover
the issue of one or more empty lines in the text file and to remove
all relevant docvariables in the document that may have been created
by a previous longer file, for I have the feeling that this is going
to be a repeat requirement. I have also added a message box at the
end so the user knows how many variables are present. If the file is
always going to be of the same length, this message box can be
omitted.
Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
ActiveDocument.Variables.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
MsgBox "Variables line1 to line" & iCount & " created"
End Sub


Peter Jamieson wrote:
If you have to resort to using a macro, the following approach might
be simpler:

Run the following macro to transfer each line into a "document
variable" so that variable "line1" contains the first line from your
file, variable "line2" contains the second line, and so on.

Then you can insert (say) the 3rd line using a field like

{ DOCVARIABLE line3 }

Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
ActiveDocument.Variables.Add "line" & iCount, strLine
Wend
Close #1
End Sub

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Bernie wrote:
Hi Graham,
Your first paragraph is correct. Can you tell me what a "merge data
format" looks like?
Then I could add that to my ascii file. I need to automate this
process so going thru Excel is not a choice.
Also, can you give me the steps for specifying how to create the
merge field into the Word doc and how do I specify to Word, to get
the data from my ascii file.
Thanks in advance for your help.

Bernie

"Graham Mayor" wrote:

I assume that the list will be merged into various places in the
same document, rather than one place in 20 different documents?
That being the case, you will need first to convert the list to a
merge data format.

This is probably simplest to do in Excel. Open the text file in
Excel then transpose the list so that the entries are all in one
row. insert a header row and name the columns A, B, C etc will
work. Save the file as an Excel document and use that as a merge data
source for a letter merge. Insert the 20 fields where you want
them. Merge to a new document.

If the list is to be merged into the same place in 20 documents,
open the list in Word convert the text to a one column table. Add a
row at the top and give the column a name. Save as a document and
use that as a merge data source.

The principles of mail merge are covered at
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will
be a merged field. There will be approx 20 lines to be merged into
various places in the document.
How do you set up the word doc to receive these fields?

[Suzanne S. Barnhill]

See http://www.gmayor.com/installing_macro.htm

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA
http://word.mvps.org

"Bernie" wrote in message
...
Thank you Peter and Graham,
What kind of macros are these? How do I run them?
From my Database I can "Launch MyWordDoc.doc" which will bring up the word
document. Then what do I do to run the macro you have most generously
provided?
P.S. the database I am using is R:Base version 7.6 I am using SQL
commands.

"Graham Mayor" wrote:

Having far too much time on my hands today and having created all those
variables in the document, a quick and easy means of inserting the
associated fields in the document would not be a bad idea so the
following
macro will do that. Each time it is run it inserts the next numbered
docvariable field at the cursor (so run it from a toolbar button of
keyboard
shortcut). It requires a further mod to Peter's macro, which is included
that counts the available fields and prevents the user from adding more
docvariable fields than he has docvariables. Re-run that macro if
necessary
to add the extra variable that stores the count.

Sub InsertLineVariable()
Dim oVars As Variables
Dim vVar As Variant
Dim aNum As Integer
Dim bNum As Integer
Dim bExists As Boolean
Dim varExists As Boolean
Set oVars = ActiveDocument.Variables
bExists = False
varExists = False
For Each vVar In oVars
If vVar.name = "numVars" Then
varExists = True
Exit For
End If
Next vVar
For Each vVar In oVars
If vVar.name = "varCount" Then
bExists = True
Exit For
End If
Next vVar
If varExists = False Then
MsgBox "There are no variables in the document" & _
" associated with this macro", vbCritical, "Error"
Exit Sub
End If
If bExists = False Then
oVars("varCount").Value = 0
End If
oVars("varCount").Value = oVars("varCount").Value + 1
aNum = oVars("varCount").Value
bNum = oVars("numVars").Value
If aNum bNum Then
MsgBox "There are no more variables to insert", _
vbInformation, "Insert Variables"
Exit Sub
End If
Selection.Fields.Add Selection.Range, _
wdFieldDocVariable, "line" & _
oVars("varCount").Value, False
End Sub



Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim bExists As Boolean
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
bExists = False
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "D:\My Documents\Test\Versions\filename.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
oVars.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
'MsgBox "Variables line1 to line" & iCount & " created"
For Each vVar In oVars
If vVar.name = "numVars" Then
bExists = True
oVars("numVars").Value = iCount
Exit For
End If
Next vVar
If bExists = False Then
oVars.Add "numVars", iCount
End If
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Graham Mayor wrote:
Good thinking - That has the makings of a plan

I have taken the liberty of making a couple of minor changes to cover
the issue of one or more empty lines in the text file and to remove
all relevant docvariables in the document that may have been created
by a previous longer file, for I have the feeling that this is going
to be a repeat requirement. I have also added a message box at the
end so the user knows how many variables are present. If the file is
always going to be of the same length, this message box can be
omitted.
Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
ActiveDocument.Variables.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
MsgBox "Variables line1 to line" & iCount & " created"
End Sub


Peter Jamieson wrote:
If you have to resort to using a macro, the following approach might
be simpler:

Run the following macro to transfer each line into a "document
variable" so that variable "line1" contains the first line from your
file, variable "line2" contains the second line, and so on.

Then you can insert (say) the 3rd line using a field like

{ DOCVARIABLE line3 }

Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
ActiveDocument.Variables.Add "line" & iCount, strLine
Wend
Close #1
End Sub

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Bernie wrote:
Hi Graham,
Your first paragraph is correct. Can you tell me what a "merge data
format" looks like?
Then I could add that to my ascii file. I need to automate this
process so going thru Excel is not a choice.
Also, can you give me the steps for specifying how to create the
merge field into the Word doc and how do I specify to Word, to get
the data from my ascii file.
Thanks in advance for your help.

Bernie

"Graham Mayor" wrote:

I assume that the list will be merged into various places in the
same document, rather than one place in 20 different documents?
That being the case, you will need first to convert the list to a
merge data format.

This is probably simplest to do in Excel. Open the text file in
Excel then transpose the list so that the entries are all in one
row. insert a header row and name the columns A, B, C etc will
work. Save the file as an Excel document and use that as a merge
data
source for a letter merge. Insert the 20 fields where you want
them. Merge to a new document.

If the list is to be merged into the same place in 20 documents,
open the list in Word convert the text to a one column table. Add a
row at the top and give the column a name. Save as a document and
use that as a merge data source.

The principles of mail merge are covered at
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will
be a merged field. There will be approx 20 lines to be merged into
various places in the document.
How do you set up the word doc to receive these fields?

[Peter Jamieson]

Jolly good, Graham.

One thing to be careful of when dealing with Doc variables is what
happens to stuff like LF characters etc. when you insert them using
DOCVARIABLE fields. I can't remember off the top of my head what the
problem is (there's a difference between Word 2007/2003 or some such
too) but didn't think it was likely to be a problem for the current
prooblem. For a more general solution you might have to modify the
strings before they go in the Variables.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Graham Mayor wrote:
Good thinking - That has the makings of a plan

I have taken the liberty of making a couple of minor changes to cover the
issue of one or more empty lines in the text file and to remove all relevant
docvariables in the document that may have been created by a previous longer
file, for I have the feeling that this is going to be a repeat requirement.
I have also added a message box at the end so the user knows how many
variables are present. If the file is always going to be of the same length,
this message box can be omitted.

Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
ActiveDocument.Variables.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
MsgBox "Variables line1 to line" & iCount & " created"
End Sub

[Graham Mayor]

Install the macros in your normal template - see
http://www.gmayor.com/installing_macro.htm (don't forget to change the path
in the line
Open "D:\My Documents\Test\Versions\filename.txt" For Input As 1
to match the path to your ascii file.)

Create a pair of toolbar buttons to run the macros as shown in that web
page.

With the document open that you wish to include the data from your ascii
file, first run the TransferTextRowsToVariables macro which creates the
variables in your document from the ascii file Next locate the positions
that you wish to insert the data in your document. Put the cursor at each
position in turn and run the macro InsertLineVariable (repeated below). This
inserts the next available numbered variable (and consequently the matching
text from your ascii file) each time the macro is run. ie { DocVariable
Line1 } { DocVariable Line2 }{ DocVariable Line3 } until all the variables
are used up. The following version of the InsertLineVariable macro has a
couple of extra lines to update the field as it is inserted and ensure the
data is displayed rather than the field construction. A limitation of the
macro as it stands is that you can only insert one field for each variable.

Sub InsertLineVariable()
Dim oVars As Variables
Dim vVar As Variant
Dim aNum As Integer
Dim bNum As Integer
Dim bExists As Boolean
Dim varExists As Boolean
Set oVars = ActiveDocument.Variables
bExists = False
varExists = False
For Each vVar In oVars
If vVar.name = "numVars" Then
varExists = True
Exit For
End If
Next vVar
For Each vVar In oVars
If vVar.name = "varCount" Then
bExists = True
Exit For
End If
Next vVar
If varExists = False Then
MsgBox "There are no variables in the document" & _
" associated with this macro", vbCritical, "Error"
Exit Sub
End If
If bExists = False Then
oVars("varCount").Value = 0
End If
oVars("varCount").Value = oVars("varCount").Value + 1
aNum = oVars("varCount").Value
bNum = oVars("numVars").Value
If aNum bNum Then
MsgBox "There are no more variables to insert", _
vbInformation, "Insert Variables"
Exit Sub
End If
Selection.Fields.Add Selection.Range, _
wdFieldDocVariable, "line" & _
oVars("varCount").Value, False
Selection.Fields.Update
ActiveWindow.View.ShowFieldCodes = False
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
Thank you Peter and Graham,
What kind of macros are these? How do I run them?
From my Database I can "Launch MyWordDoc.doc" which will bring up the
word document. Then what do I do to run the macro you have most
generously provided?
P.S. the database I am using is R:Base version 7.6 I am using SQL
commands.

"Graham Mayor" wrote:

Having far too much time on my hands today and having created all
those variables in the document, a quick and easy means of inserting
the associated fields in the document would not be a bad idea so the
following macro will do that. Each time it is run it inserts the
next numbered docvariable field at the cursor (so run it from a
toolbar button of keyboard shortcut). It requires a further mod to
Peter's macro, which is included that counts the available fields
and prevents the user from adding more docvariable fields than he
has docvariables. Re-run that macro if necessary to add the extra
variable that stores the count.

Sub InsertLineVariable()
Dim oVars As Variables
Dim vVar As Variant
Dim aNum As Integer
Dim bNum As Integer
Dim bExists As Boolean
Dim varExists As Boolean
Set oVars = ActiveDocument.Variables
bExists = False
varExists = False
For Each vVar In oVars
If vVar.name = "numVars" Then
varExists = True
Exit For
End If
Next vVar
For Each vVar In oVars
If vVar.name = "varCount" Then
bExists = True
Exit For
End If
Next vVar
If varExists = False Then
MsgBox "There are no variables in the document" & _
" associated with this macro", vbCritical, "Error"
Exit Sub
End If
If bExists = False Then
oVars("varCount").Value = 0
End If
oVars("varCount").Value = oVars("varCount").Value + 1
aNum = oVars("varCount").Value
bNum = oVars("numVars").Value
If aNum bNum Then
MsgBox "There are no more variables to insert", _
vbInformation, "Insert Variables"
Exit Sub
End If
Selection.Fields.Add Selection.Range, _
wdFieldDocVariable, "line" & _
oVars("varCount").Value, False
End Sub



Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim bExists As Boolean
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
bExists = False
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "D:\My Documents\Test\Versions\filename.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
oVars.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
'MsgBox "Variables line1 to line" & iCount & " created"
For Each vVar In oVars
If vVar.name = "numVars" Then
bExists = True
oVars("numVars").Value = iCount
Exit For
End If
Next vVar
If bExists = False Then
oVars.Add "numVars", iCount
End If
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Graham Mayor wrote:
Good thinking - That has the makings of a plan

I have taken the liberty of making a couple of minor changes to
cover the issue of one or more empty lines in the text file and to
remove all relevant docvariables in the document that may have been
created by a previous longer file, for I have the feeling that this
is going to be a repeat requirement. I have also added a message
box at the end so the user knows how many variables are present. If
the file is always going to be of the same length, this message box
can be omitted.
Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
ActiveDocument.Variables.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
MsgBox "Variables line1 to line" & iCount & " created"
End Sub


Peter Jamieson wrote:
If you have to resort to using a macro, the following approach
might be simpler:

Run the following macro to transfer each line into a "document
variable" so that variable "line1" contains the first line from
your file, variable "line2" contains the second line, and so on.

Then you can insert (say) the 3rd line using a field like

{ DOCVARIABLE line3 }

Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
ActiveDocument.Variables.Add "line" & iCount, strLine
Wend
Close #1
End Sub

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Bernie wrote:
Hi Graham,
Your first paragraph is correct. Can you tell me what a "merge
data format" looks like?
Then I could add that to my ascii file. I need to automate this
process so going thru Excel is not a choice.
Also, can you give me the steps for specifying how to create the
merge field into the Word doc and how do I specify to Word, to get
the data from my ascii file.
Thanks in advance for your help.

Bernie

"Graham Mayor" wrote:

I assume that the list will be merged into various places in the
same document, rather than one place in 20 different documents?
That being the case, you will need first to convert the list to a
merge data format.

This is probably simplest to do in Excel. Open the text file in
Excel then transpose the list so that the entries are all in one
row. insert a header row and name the columns A, B, C etc will
work. Save the file as an Excel document and use that as a merge
data source for a letter merge. Insert the 20 fields where you
want
them. Merge to a new document.

If the list is to be merged into the same place in 20 documents,
open the list in Word convert the text to a one column table.
Add a row at the top and give the column a name. Save as a
document and use that as a merge data source.

The principles of mail merge are covered at
http://www.gmayor.com/mail_merge_lab...th_word_xp.htm or
http://www.gmayor.com/merge_labels_with_word_2007.htm
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Bernie wrote:
I want to merge fields from an ascii file into a word document.
The ascii file has a word or phrase on each line. Each line will
be a merged field. There will be approx 20 lines to be merged
into various places in the document.
How do you set up the word doc to receive these fields?

[Graham Mayor]

I tested with an ascii file (created from the PrintFolders utility that can
be downloaded from my web site) which does not produce paragraph endings of
the type used in Word, but which are CHR(13) which only look like paragraph
endings and thus when imported into Word does not make paragraphs that vba
can act upon.. This was the reason for my rather blunt approach in the first
macro. Reading the ascii file directly negated that approach. I haven't yet
tested it with line feed characters (CHR(11).

I think we need to see if this approach will work for the OP before putting
any more effort into fine tuning the macros.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Peter Jamieson wrote:
Jolly good, Graham.

One thing to be careful of when dealing with Doc variables is what
happens to stuff like LF characters etc. when you insert them using
DOCVARIABLE fields. I can't remember off the top of my head what the
problem is (there's a difference between Word 2007/2003 or some such
too) but didn't think it was likely to be a problem for the current
prooblem. For a more general solution you might have to modify the
strings before they go in the Variables.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Graham Mayor wrote:
Good thinking - That has the makings of a plan

I have taken the liberty of making a couple of minor changes to
cover the issue of one or more empty lines in the text file and to
remove all relevant docvariables in the document that may have been
created by a previous longer file, for I have the feeling that this
is going to be a repeat requirement. I have also added a message box
at the end so the user knows how many variables are present. If the
file is always going to be of the same length, this message box can
be omitted. Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
ActiveDocument.Variables.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
MsgBox "Variables line1 to line" & iCount & " created"
End Sub

[Bernie]

Hi Everyone,
I really appreciate all this advice.
I have it working -- sort of --
I get this message box:
"Opening this document will run the following SQL command sel * from
D:\....\Merge.doc data from your database will be placed in the document, do
you want to continue"
Of course I want to continue, but is there a way to eliminate this nuisance
message?

Furthermore when continuing I get:
Record 1 contained too few data fields then I get another message
record 1 contained too few field delimiters (,) .
Then another box comes up titled "Header Record Delimiters" it shows , &
(Enter)
I just click OK and my document appears with all the fields filled in just
the way i want it.
So what can I do to eliminate these messages?

"Graham Mayor" wrote:

I tested with an ascii file (created from the PrintFolders utility that can
be downloaded from my web site) which does not produce paragraph endings of
the type used in Word, but which are CHR(13) which only look like paragraph
endings and thus when imported into Word does not make paragraphs that vba
can act upon.. This was the reason for my rather blunt approach in the first
macro. Reading the ascii file directly negated that approach. I haven't yet
tested it with line feed characters (CHR(11).

I think we need to see if this approach will work for the OP before putting
any more effort into fine tuning the macros.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Peter Jamieson wrote:
Jolly good, Graham.

One thing to be careful of when dealing with Doc variables is what
happens to stuff like LF characters etc. when you insert them using
DOCVARIABLE fields. I can't remember off the top of my head what the
problem is (there's a difference between Word 2007/2003 or some such
too) but didn't think it was likely to be a problem for the current
prooblem. For a more general solution you might have to modify the
strings before they go in the Variables.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Graham Mayor wrote:
Good thinking - That has the makings of a plan

I have taken the liberty of making a couple of minor changes to
cover the issue of one or more empty lines in the text file and to
remove all relevant docvariables in the document that may have been
created by a previous longer file, for I have the feeling that this
is going to be a repeat requirement. I have also added a message box
at the end so the user knows how many variables are present. If the
file is always going to be of the same length, this message box can
be omitted. Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
ActiveDocument.Variables.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
MsgBox "Variables line1 to line" & iCount & " created"
End Sub

[Peter Jamieson]

First, let's keep two things separate.

1. The approach Graham originally suggested uses Mailmerge and Mailmerge
fields. When your document is connected to a mailmerge data source and
you re-open it (and even sometimes when it isn't, in some versions of
Word), you get that SQL message. One way to get rid of it is to apply
the registry change described in the following KB article:

http://support.microsoft.com/kb/825765

It sounds like you are currently using that approach. To see what is
causing the additional problems, you should probably try opening the
data source that Graham's macro produces - AFAICS it should be a .doc or
..docx Word document containing a table with one row containing field
names, and a second row containing your data. It might or might not
become clear what is wrong, but for example there shouldn't be any empty
cells.

2. The approach I suggested and which Graham has added to does not rely
on mailmerge, uses DOCVARIABLE fields rather than mailmerge fields, and
does not result in the SQL message. If you haven't tried it, I suggest
you do so now. However, if you have already connected your document to a
mailmerge data source and saved it, you will still get that SQL message
when you re-open it. If possible, start afresh with a new document.




Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Bernie wrote:
Hi Everyone,
I really appreciate all this advice.
I have it working -- sort of --
I get this message box:
"Opening this document will run the following SQL command sel * from
D:\....\Merge.doc data from your database will be placed in the document, do
you want to continue"
Of course I want to continue, but is there a way to eliminate this nuisance
message?

Furthermore when continuing I get:
Record 1 contained too few data fields then I get another message
record 1 contained too few field delimiters (,) .
Then another box comes up titled "Header Record Delimiters" it shows , &
(Enter)
I just click OK and my document appears with all the fields filled in just
the way i want it.
So what can I do to eliminate these messages?

"Graham Mayor" wrote:

I tested with an ascii file (created from the PrintFolders utility that can
be downloaded from my web site) which does not produce paragraph endings of
the type used in Word, but which are CHR(13) which only look like paragraph
endings and thus when imported into Word does not make paragraphs that vba
can act upon.. This was the reason for my rather blunt approach in the first
macro. Reading the ascii file directly negated that approach. I haven't yet
tested it with line feed characters (CHR(11).

I think we need to see if this approach will work for the OP before putting
any more effort into fine tuning the macros.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Peter Jamieson wrote:
Jolly good, Graham.

One thing to be careful of when dealing with Doc variables is what
happens to stuff like LF characters etc. when you insert them using
DOCVARIABLE fields. I can't remember off the top of my head what the
problem is (there's a difference between Word 2007/2003 or some such
too) but didn't think it was likely to be a problem for the current
prooblem. For a more general solution you might have to modify the
strings before they go in the Variables.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Graham Mayor wrote:
Good thinking - That has the makings of a plan

I have taken the liberty of making a couple of minor changes to
cover the issue of one or more empty lines in the text file and to
remove all relevant docvariables in the document that may have been
created by a previous longer file, for I have the feeling that this
is going to be a repeat requirement. I have also added a message box
at the end so the user knows how many variables are present. If the
file is always going to be of the same length, this message box can
be omitted. Sub TransferTextRowsToVariables()
Dim iCount As Integer
Dim strLine As String
Dim vVar As Variant
Dim oVars As Variables
Set oVars = ActiveDocument.Variables
For Each vVar In oVars
If InStr(1, vVar.name, "line") Then
vVar.Delete
End If
Next vVar
' use your file name here
Open "c:\a\ascii.txt" For Input As 1
iCount = 0
While Not EOF(1)
Line Input #1, strLine
iCount = iCount + 1
On Error Resume Next
'ActiveDocument.Variables("line" & iCount).Delete
Err.Clear
On Error GoTo 0
If Len(strLine) 0 Then
ActiveDocument.Variables.Add "line" & iCount, strLine
Else
iCount = iCount - 1
End If
Wend
Close #1
MsgBox "Variables line1 to line" & iCount & " created"
End Sub

[Graham Mayor]

Peter Jamieson wrote:
However, if you have already connected
your document to a mailmerge data source and saved it, you will still
get that SQL message when you re-open it. If possible, start afresh
with a new document.

To save re-typing change the document type to Normal Word document from the
mailmerge toolbar or the mailings tab which should disconnect the data
source.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org