[salventuro]

Hi everyone,

Hope everyone is having a good day.

This is my first post here, I'm a noob, so be gentle. LOL

I am running Office 2003 Pro on XP Pro SP2.

I have been searching the web for a solution to a project I'm working on, to no avail. I don't know if it's even possible.

I have inserted the image of a form in a Word doc, I have mapped the fields to be merged from the data in excel spreadsheets

I can perform the mail merge manually. The issue is, I have over one thousand excel doc's to perform the merge on. Can a macro be used to loop through a folder, merge and propogate to a new Word doc for each excel doc?


This will create over one thousand word doc's, which will then be emailed to the corresponding employees.

Also, can the name of the new Word doc created, be saved as the corresponding name as the Excel doc?
Example, if the Excel doc is named 123abc.xls. can the new propogated Word doc be saved as 123abc.doc?

Any assistance or links anyone knows of, would be greatly appreciated.

Thanks and have a great day!

[Doug Robbins - Word MVP]

It sounds like you are actually creating only one document from each Excel
spreadsheet, which is not really the purpose of mailmerge, though it can be
used for that purpose.

Regardless, to create each of the documents that you want to create from
each of the Excel spreadsheets, you are going to need to use a macro (VBA)
for the purpose.

You will find some information on batch processing all of the files in a
folder in the article "Find & ReplaceAll on a batch of documents in the same
folder" at:
http://www.word.mvps.org/FAQs/MacrosVBA/BatchFR.htm which while it deals
with processing Word documents, the principle is the same (use of the Dir
function)

Of course, each of the spreadsheets are going to need to be identical in
layout to be able to successfully process them and more information would be
needed on the way in which the data that you want to use is contained the
spreadsheets to give you any more specific assistance.

To answer your question about the naming of the resulting Word files, yes
that is certainly possible.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"salventuro" wrote in message
...

Hi everyone,

Hope everyone is having a good day.

This is my first post here, I'm a noob, so be gentle. LOL

I am running Office 2003 Pro on XP Pro SP2.

I have been searching the web for a solution to a project I'm working
on, to no avail. I don't know if it's even possible.

I have inserted the image of a form in a Word doc, I have mapped the
fields to be merged from the data in excel spreadsheets

I can perform the mail merge manually. The issue is, I have over one
thousand excel doc's to perform the merge on. Can a macro be used to
loop through a folder, merge and propogate to a new Wrd doc for each
excel doc?


This will create over one thousand word doc's, which will then be
emailed to the corresponding employees.

Also, can the name of the new Word doc created, be saved as the
corresponding name as the Excel doc?
Example, if the Excel doc is named 123abc.xls. can the new propogated
Word doc be saved as 123ab.doc?

Any assistance or links anyone knows of, would be greatly appreciated.

Thanks and have a great day!




--
salventuro

[salventuro]

Hi Doug Robbins. Hi macropod.

Thank you for the replies.

I navigated to the link you provided, it looks good.

Doug, yes, the information in each of the spreadsheets is going to be identical in layout, all on sheet1.

There are six merge fields, which are used from each Excel workbook, sheet1.

I have placed six text boxes, (where a merge field is inserted), on top of a picture of a form, in a Word doc.

The merge works, I can propagate the docs successfully, individually.

You are also correct in that I am creating only one document, from each Excel spreadsheet.

I hope I have provided enough additional information to paint a clearer picture for you. If not, please let me know. If need be I can provide a mock sample of the Excel and Word docs.

I will try the VBA, if successful; I will post the final code so others might use it.
If I am not successful, I will definitely be back seeking your guidance.

Doug, any tips or pointers you can provide, regarding the added information you requested. would be greatly appreciated.

macropod,

The link solution you mention sounds great.

The Excel cells are always in the same location in each of the workbooks.
I run a macro, to rename all the original Excel files, the name is taken from three cells from a row in Excel. Is this similar to what you are suggesting?

If yes, can you point me in the direction on how to apply it in Word?

Thank you both again for the information and your time, I truly appreciate it.

Have a great day!


Sal.

Originally Posted by Doug Robbins - Word MVP

It sounds like you are actually creating only one document from each Excel
spreadsheet, which is not really the purpose of mailmerge, though it can be
used for that purpose.

Regardless, to create each of the documents that you want to create from
each of the Excel spreadsheets, you are going to need to use a macro (VBA)
for the purpose.

You will find some information on batch processing all of the files in a
folder in the article "Find & ReplaceAll on a batch of documents in the same
folder" at:
http://www.word.mvps.org/FAQs/MacrosVBA/BatchFR.htm which while it deals
with processing Word documents, the principle is the same (use of the Dir
function)

Of course, each of the spreadsheets are going to need to be identical in
layout to be able to successfully process them and more information would be
needed on the way in which the data that you want to use is contained the
spreadsheets to give you any more specific assistance.

To answer your question about the naming of the resulting Word files, yes
that is certainly possible.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"salventuro" wrote in message
...

Hi everyone,

Hope everyone is having a good day.

This is my first post here, I'm a noob, so be gentle. LOL

I am running Office 2003 Pro on XP Pro SP2.

I have been searching the web for a solution to a project I'm working
on, to no avail. I don't know if it's even possible.

I have inserted the image of a form in a Word doc, I have mapped the
fields to be merged from the data in excel spreadsheets

I can perform the mail merge manually. The issue is, I have over one
thousand excel doc's to perform the merge on. Can a macro be used to
loop through a folder, merge and propogate to a new Wrd doc for each
excel doc?


This will create over one thousand word doc's, which will then be
emailed to the corresponding employees.

Also, can the name of the new Word doc created, be saved as the
corresponding name as the Excel doc?
Example, if the Excel doc is named 123abc.xls. can the new propogated
Word doc be saved as 123ab.doc?

Any assistance or links anyone knows of, would be greatly appreciated.

Thanks and have a great day!




--
salventuro

[macropod[_2_]]

Hi salventuro,

If the Excel cells you want to read from are always in the same location in each of the workbooks, a better approach might be to use
a template that has a LINK field pointing to any file in the source folder, then simply use the macro to change the linked file
names to correspond with each of the Excel files and save the file as you've proposed with the same name as the Excel workbook
(different extension, of course).

One potential advantage of this over a mailmerge is that the Excel cells don't all ahve to be on the one worksheet. Another is that,
once the files are created and provided you leave the links intact, later changes in the Excel workbooks will be reflected in the
corresponding documents.

--
Cheers
macropod
[Microsoft MVP - Word]


"salventuro" wrote in message ...

Hi everyone,

Hope everyone is having a good day.

This is my first post here, I'm a noob, so be gentle. LOL

I am running Office 2003 Pro on XP Pro SP2.

I have been searching the web for a solution to a project I'm working
on, to no avail. I don't know if it's even possible.

I have inserted the image of a form in a Word doc, I have mapped the
fields to be merged from the data in excel spreadsheets

I can perform the mail merge manually. The issue is, I have over one
thousand excel doc's to perform the merge on. Can a macro be used to
loop through a folder, merge and propogate to a new Wrd doc for each
excel doc?


This will create over one thousand word doc's, which will then be
emailed to the corresponding employees.

Also, can the name of the new Word doc created, be saved as the
corresponding name as the Excel doc?
Example, if the Excel doc is named 123abc.xls. can the new propogated
Word doc be saved as 123ab.doc?

Any assistance or links anyone knows of, would be greatly appreciated.

Thanks and have a great day!




--
salventuro

[salventuro]

Hi Doug Robbins. Hi macropod.

Thank you for the replies.

I navigated to the link you provided, it looks good.

Doug, yes, the information in each of the spreadsheets is going to be identical in layout, all on sheet1.

There are six merge fields, which are used from each Excel workbook, sheet1.

I have placed six text boxes, (where a merge field is inserted), on top of a picture of a form, in a Word doc.

The merge works, I can propagate the docs successfully, individually.

You are also correct in that I am creating only one document, from each Excel spreadsheet.

I hope I have provided enough additional information to paint a clearer picture for you. If not, please let me know. If need be I can provide a mock sample of the Excel and Word docs.

I will try the VBA, if successful; I will post the final code so others might use it.
If I am not successful, I will definitely be back seeking your guidance.

Doug, any tips or pointers you can provide, regarding the added information you requested. would be greatly appreciated.

macropod,

The link solution you mention sounds great.

The Excel cells are always in the same location in each of the workbooks.
I run a macro, to rename all the original Excel files, the name is taken from three cells from a row in Excel. Is this similar to what you are suggesting?

If yes, can you point me in the direction on how to apply it in Word?

Thank you both again for the information and your time, I truly appreciate it.

Have a great day!

Sal.

Originally Posted by macropod[_2_]

Hi salventuro,

If the Excel cells you want to read from are always in the same location in each of the workbooks, a better approach might be to use
a template that has a LINK field pointing to any file in the source folder, then simply use the macro to change the linked file
names to correspond with each of the Excel files and save the file as you've proposed with the same name as the Excel workbook
(different extension, of course).

One potential advantage of this over a mailmerge is that the Excel cells don't all ahve to be on the one worksheet. Another is that,
once the files are created and provided you leave the links intact, later changes in the Excel workbooks will be reflected in the
corresponding documents.

--
Cheers
macropod
[Microsoft MVP - Word]


"salventuro" wrote in message ...

Hi everyone,

Hope everyone is having a good day.

This is my first post here, I'm a noob, so be gentle. LOL

I am running Office 2003 Pro on XP Pro SP2.

I have been searching the web for a solution to a project I'm working
on, to no avail. I don't know if it's even possible.

I have inserted the image of a form in a Word doc, I have mapped the
fields to be merged from the data in excel spreadsheets

I can perform the mail merge manually. The issue is, I have over one
thousand excel doc's to perform the merge on. Can a macro be used to
loop through a folder, merge and propogate to a new Wrd doc for each
excel doc?


This will create over one thousand word doc's, which will then be
emailed to the corresponding employees.

Also, can the name of the new Word doc created, be saved as the
corresponding name as the Excel doc?
Example, if the Excel doc is named 123abc.xls. can the new propogated
Word doc be saved as 123ab.doc?

Any assistance or links anyone knows of, would be greatly appreciated.

Thanks and have a great day!




--
salventuro

[salventuro]

The message you are about to post is viewed in a number of different ways, not just through WordBanter.

Originally Posted by salventuro

Hi everyone,

Hope everyone is having a good day.

This is my first post here, I'm a noob, so be gentle. LOL

I am running Office 2003 Pro on XP Pro SP2.

I have been searching the web for a solution to a project I'm working on, to no avail. I don't know if it's even possible.

I have inserted the image of a form in a Word doc, I have mapped the fields to be merged from the data in excel spreadsheets

I can perform the mail merge manually. The issue is, I have over one thousand excel doc's to perform the merge on. Can a macro be used to loop through a folder, merge and propogate to a new Word doc for each excel doc?


This will create over one thousand word doc's, which will then be emailed to the corresponding employees.

Also, can the name of the new Word doc created, be saved as the corresponding name as the Excel doc?
Example, if the Excel doc is named 123abc.xls. can the new propogated Word doc be saved as 123abc.doc?

Any assistance or links anyone knows of, would be greatly appreciated.

Thanks and have a great day!

[salventuro]

Hi Doug, Hi macropod.

Thanks for your replies. Sorry for not being a little bit clearer.

Doug, yes, each of the spreadsheets are identical in layout, all data is in Sheet1, in each Excel workbook.
What I have done is, inserted six text fields in the Word document, that are mapped to Excel.


macropod, thank you for the information. You mentioned, "LINK field pointing to any file in the source folder"
Yes, the Excel cells always in the same location in each of the workbooks. Could you please point me in the right direction for this?

Again, my sincerest thanks for your time and assistance, it is very much appreciated

[salventuro]

Hi everyone,

It appears I am guilty of cross posting without notifying the Forum. My apology for this.

I posted the same question at "http://www.vbaexpress.com/forum/showthread.php?t=26691".

I did not mean to be rude.

Originally Posted by salventuro

Hi Doug, Hi macropod.

Thanks for your replies. Sorry for not being a little bit clearer.

Doug, yes, each of the spreadsheets are identical in layout, all data is in Sheet1, in each Excel workbook.
What I have done is, inserted six text fields in the Word document, that are mapped to Excel.


macropod, thank you for the information. You mentioned, "LINK field pointing to any file in the source folder"
Yes, the Excel cells always in the same location in each of the workbooks. Could you please point me in the right direction for this?

Again, my sincerest thanks for your time and assistance, it is very much appreciated

[salventuro]

Hi Doug,

Thanks for the reply.
I have not resolved the issue as of yet, I am having one heck of a time with this one.

Any light you can shine on this for me, I would be grateful.

Have a great day!

Sal.

[macropod[_2_]]

Hi salventuro,

OK, so what have you tried and what part(s) are you having difficulty with? Doug and I have suggested different approaches you might
take.

--
Cheers
macropod
[Microsoft MVP - Word]


"salventuro" wrote in message ...

Hi Doug,

Thanks for the reply.
I have not resolved the issue as of yet, I am having one heck of a time
with this one.

Any light you can shine on this for me, I would be grateful.

Have a great day!

Sal.




--
salventuro

[Doug Robbins - Word MVP]

So is your problem sorted or not.

As the other forum to which you posted the question requires one to register
to view the content, I will not visit it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"salventuro" wrote in message
...

Hi everyone,

It appears I am guilty of cross posting without notifying the Forum. My
apology for this.

I posted the same question at
"http://www.vbaexpress.com/forum/showthread.php?t=26691".

I did not mean to be rude.

salventuro;412617 Wrote:
Hi Doug, Hi macropod.

Thanks for your replies. Sorry for not being a little bit clearer.

Doug, yes, each of the spreadsheets are identical in layout, all data
is in Sheet1, in each Excel workbook.
What I have done is, inserted six text fields in the Word document,
that are mapped to Excel.


macropod, thank you for the information. You mentioned, "LINK field
pointing to any file in the source folder"
Yes, the Excel cells always in the same location in each of the
workbooks. Could you please point me in the right direction for this?

Again, my sincerest thanks for your time and assistance, it is very
much appreciated




--
salventuro

[salventuro]

Hi Doug,

Thanks for the reply.
I have not resolved the issue as of yet, I am having one heck of a
time with this one.

Any light you can shine on this for me, I would be grateful.

Have a great day! Below is my post at the other Forum, after someone
mentioned I should have mentioned this post. .

Sal.

Hi everyone,

It appears I am guilty of cross posting without notifying the Forum. My
apology for this.

I posted the same question at
"http://www.vbaexpress.com/forum/showthread.php?t=26691".

I did not mean to be rude.

salventuro;412617 Wrote:
Hi Doug, Hi macropod.

Thanks for your replies. Sorry for not being a little bit clearer.

Doug, yes,eachof the spreadsheets are identical in layout, all data
is in Sheet1, ineachExcelworkbook.
What I have done is, inserted six text fields in the Word document,
that are mapped toExcel.

macropod, thank you for the information. You mentioned, "LINK field
pointing to any file in the sourcefolder"
Yes, theExcelcells always in the same location ineachof the
workbooks. Could you please point me in the right direction for this?

Again, my sincerest thanks for your time and assistance, it is very
much appreciated

--
salventuro- Hide quoted text -

- Show quoted text -
Hi macropod,

Yes, I have posted the same question on wordbanter, Great site by the
way. Point taken, I should have mentioned that.

I had difficulties posting a follow up there, as you saw I posted once
in error and then provided more info, when Doug mentioned I should
have provided more details. (Thanks again Doug).

I tried to post to the newsgroup directly, but, got an error, "posting
not allowed or permited". I have been subscribing to newsgroups for
over five years and never got that error message.

I have been searching many sites, trying to get a solution to this. In
my searches I came accross this site, it looked very good, so I
decided to sign up and post here also.

I also noticed you are every where on the web, providing a huge amout
of assistance to others. When I read your response here, I realised I
looked like a bit of jerk for not mentioning it. When I posted here I
didn't think anyone would be interested in my other post. But now I
can see how that can be viewed as a no-no, to all the experts who
provide information and assistance, for no fee and on their time.

I have a website cbtlessons (I can't post links yet) just add www and
dot com), where I provide video instructions for newer PC users (and
math for my kids and their friends). I pay for it out of my pocket and
it's free to anyone.

Unlike these boards, where there are much more knowledge people than
me, I don't have interaction with anyone who visits my web page.

So I dropped the ball here, I should have had more netiquette, and
taken into account the community. My apology for this.

Thanks for the info on the the other fourm by the way. I am studying
up on it, but am no closer to a solution.

Anyway, I hope everyone has a great day.
Gotta go now, my kids are fighting again, where's my ref whistle!

Sal.


On May 14, 1:10*pm, "Doug Robbins - Word MVP"
wrote:
So is your problem sorted or not.

As the other forum to which you posted the question requires one to register
to view the content, I will not visit it.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com"salventuro" wrote in message

...

[MonMonRie]

How do I modify my mail merge file? I would like to add another field to the
mail merge file. Can anyone help me?

--
MonMonRie


"salventuro" wrote:


Hi everyone,

Hope everyone is having a good day.

This is my first post here, I'm a noob, so be gentle. LOL

I am running Office 2003 Pro on XP Pro SP2.

I have been searching the web for a solution to a project I'm working
on, to no avail. I don't know if it's even possible.

I have inserted the image of a form in a Word doc, I have mapped the
fields to be merged from the data in excel spreadsheets

I can perform the mail merge manually. The issue is, I have over one
thousand excel doc's to perform the merge on. Can a macro be used to
loop through a folder, merge and propogate to a new Wrd doc for each
excel doc?


This will create over one thousand word doc's, which will then be
emailed to the corresponding employees.

Also, can the name of the new Word doc created, be saved as the
corresponding name as the Excel doc?
Example, if the Excel doc is named 123abc.xls. can the new propogated
Word doc be saved as 123ab.doc?

Any assistance or links anyone knows of, would be greatly appreciated.

Thanks and have a great day!




--
salventuro

[Doug Robbins - Word MVP]

What version of Word, what type of Data Source?

However, one certain way of doing it is to use the existing data source with
a Directory type mailmerge main document in which you insert the mergefields
into the cells of a one row table that has one extra column, that is
required, but have nothing else in the document. When you execute that
merge to a new document, that document will contain a table with a row of
data for each record in the data source. Insert a row at the top of that
table and into the cells of that row insert the names of your mergefields
(just the names as in your data source, not the actual mergefields) and then
populate the additional empty column with the data that you require for each
record. You can then save and use that file as a data source.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
"MonMonRie" wrote in message
...
How do I modify my mail merge file? I would like to add another field to
the
mail merge file. Can anyone help me?

--
MonMonRie


"salventuro" wrote:


Hi everyone,

Hope everyone is having a good day.

This is my first post here, I'm a noob, so be gentle. LOL

I am running Office 2003 Pro on XP Pro SP2.

I have been searching the web for a solution to a project I'm working
on, to no avail. I don't know if it's even possible.

I have inserted the image of a form in a Word doc, I have mapped the
fields to be merged from the data in excel spreadsheets

I can perform the mail merge manually. The issue is, I have over one
thousand excel doc's to perform the merge on. Can a macro be used to
loop through a folder, merge and propogate to a new Wrd doc for each
excel doc?


This will create over one thousand word doc's, which will then be
emailed to the corresponding employees.

Also, can the name of the new Word doc created, be saved as the
corresponding name as the Excel doc?
Example, if the Excel doc is named 123abc.xls. can the new propogated
Word doc be saved as 123ab.doc?

Any assistance or links anyone knows of, would be greatly appreciated.

Thanks and have a great day!




--
salventuro