Excel VBA

[gimolu]

HI,

I need some help building a macro.
In excel let's suppose that I have the following table:

Product Description Quantity Path to details
Product 1 0
\\server\product1_description.doc
Product 2 1
\\server\product2_description.doc
Product 3 2
\\server\product3_description.doc

Now, I want to have a button that when someone clicks on it, it will call a
macro that verifies the Quantity column and if the quantity is >=1 it will
generate a word document and merge the product description (path to details
column) of the products that have a quantity >=1.

I have no idea where to start... I can't find the write vba command to merge
multiple .doc documents.
Any help would be much appreciated!

By the way, I started the macro with this lines of code:

Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "d:\test.doc"
wordapp.Visible = True

This will create the "Main" document. I don't know how I can merge other
..doc in this "Main" file.
Thanks in advanced.

Luis Ramos

 

[macropod]

Hi gimolu,

You don't really need a macro for this - you can do much the same using Word's mailmerge facility, combined with a SKIPIF field, to
generate a set of product descriptions for all products where Quantity > 0. The SKIPIF field would be coded as:
{SKIPIF {MERGEFIELD Quantity}< 1}
or
{SKIPIF «Quantity»< 1}
You could use '= 0' instead of '< 1', but the latter allows for the posibility that the Quantity is a calculated value and someone's
messed up one of the inputs, leading to negative values.

Note: The field brace pairs (ie '{ }') for the above examples are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message. Likwise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual mergefields,
which you can insert from the mailmerge toolbar.

 

[gimolu]

Hi macropod,

I've searched for the mailmerge functionality and it seems a possibility to
achieve what I want to do.
However this spreadsheet is used by many people and I want to keep it simple
for users. If I present the mailmerge to my end-users they will freak out!
So I would like to implement a macro to do this!
I found these two functions importing the MS word 12 to the Excel VBA
references:

Dim firstest As Word.Document
firstest.Merge \\path\to\document.doc, etc, etc, etc

Dim secondtest As Word.Application
secondtest.MergeDocuments (etc, etc, etc)

Do you have any clue how can I use VBA to do this?
I'm getting an error every time I compile the macro!

Thanks!

Hi gimolu,

You don't really need a macro for this - you can do much the same using
Word's mailmerge facility, combined with a SKIPIF field, to generate a set
of product descriptions for all products where Quantity > 0. The SKIPIF
field would be coded as:
{SKIPIF {MERGEFIELD Quantity}< 1}
or
{SKIPIF «Quantity»< 1}
You could use '= 0' instead of '< 1', but the latter allows for the
posibility that the Quantity is a calculated value and someone's messed up
one of the inputs, leading to negative values.

Note: The field brace pairs (ie '{ }') for the above examples are created
via Ctrl-F9 - you can't simply type them or copy & paste them from this
message. Likwise, you can't type or copy & paste the chevrons (ie '« »') -
they're part of the actual mergefields, which you can insert from the
mailmerge toolbar.


--
Cheers
macropod
[Microsoft MVP - Word]

HI,

I need some help building a macro.
In excel let's suppose that I have the following table:

Product Description Quantity Path to details
Product 1 0 \\server\product1_description.doc
Product 2 1 \\server\product2_description.doc
Product 3 2 \\server\product3_description.doc

Now, I want to have a button that when someone clicks on it, it will call
a
macro that verifies the Quantity column and if the quantity is >=1 it
will
generate a word document and merge the product description (path to
details
column) of the products that have a quantity >=1.

I have no idea where to start... I can't find the write vba command to
merge
multiple .doc documents.
Any help would be much appreciated!

By the way, I started the macro with this lines of code:

Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "d:\test.doc"
wordapp.Visible = True

This will create the "Main" document. I don't know how I can merge other
.doc in this "Main" file.
Thanks in advanced.

Luis Ramos

 

[Doug Robbins - Word MVP]

As a minimum, you will need to set a reference to the Word Object Library
via Tools>References in the Visual Basic Editor from Excel.

See the article "Control Word from Excel" at:
http://www.word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm


--
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

Hi macropod,

I've searched for the mailmerge functionality and it seems a possibility
to achieve what I want to do.
However this spreadsheet is used by many people and I want to keep it
simple for users. If I present the mailmerge to my end-users they will
freak out!
So I would like to implement a macro to do this!
I found these two functions importing the MS word 12 to the Excel VBA
references:

Dim firstest As Word.Document
firstest.Merge \\path\to\document.doc, etc, etc, etc

Dim secondtest As Word.Application
secondtest.MergeDocuments (etc, etc, etc)

Do you have any clue how can I use VBA to do this?
I'm getting an error every time I compile the macro!

Thanks!

Hi gimolu,

You don't really need a macro for this - you can do much the same using
Word's mailmerge facility, combined with a SKIPIF field, to generate a
set of product descriptions for all products where Quantity > 0. The
SKIPIF field would be coded as:
{SKIPIF {MERGEFIELD Quantity}< 1}
or
{SKIPIF «Quantity»< 1}
You could use '= 0' instead of '< 1', but the latter allows for the
posibility that the Quantity is a calculated value and someone's messed
up one of the inputs, leading to negative values.

Note: The field brace pairs (ie '{ }') for the above examples are created
via Ctrl-F9 - you can't simply type them or copy & paste them from this
message. Likwise, you can't type or copy & paste the chevrons (ie
'« »') - they're part of the actual mergefields, which you can insert
from the mailmerge toolbar.


--
Cheers
macropod
[Microsoft MVP - Word]

HI,

I need some help building a macro.
In excel let's suppose that I have the following table:

Product Description Quantity Path to details
Product 1 0 \\server\product1_description.doc
Product 2 1 \\server\product2_description.doc
Product 3 2 \\server\product3_description.doc

Now, I want to have a button that when someone clicks on it, it will
call a
macro that verifies the Quantity column and if the quantity is >=1 it
will
generate a word document and merge the product description (path to
details
column) of the products that have a quantity >=1.

I have no idea where to start... I can't find the write vba command to
merge
multiple .doc documents.
Any help would be much appreciated!

By the way, I started the macro with this lines of code:

Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "d:\test.doc"
wordapp.Visible = True

This will create the "Main" document. I don't know how I can merge other
.doc in this "Main" file.
Thanks in advanced.

Luis Ramos

 

[macropod]

Hi gimolu,

Once you've created the mailmerge main document, including attaching it to the Excel data source and inserting the SKIPIF field, all
your users will have to do is to open the document and click on 'Merge'. That gives them the choice of whether to merge to a new
document or direct to the printer, plus some other options that may also be useful at times. With a mailmerge, none of the macro
security issues arise.

--
Cheers
macropod
[Microsoft MVP - Word]

Hi macropod,

I've searched for the mailmerge functionality and it seems a possibility to achieve what I want to do.
However this spreadsheet is used by many people and I want to keep it simple for users. If I present the mailmerge to my end-users
they will freak out!
So I would like to implement a macro to do this!
I found these two functions importing the MS word 12 to the Excel VBA references:

Dim firstest As Word.Document
firstest.Merge \\path\to\document.doc, etc, etc, etc

Dim secondtest As Word.Application
secondtest.MergeDocuments (etc, etc, etc)

Do you have any clue how can I use VBA to do this?
I'm getting an error every time I compile the macro!

Thanks!

Hi gimolu,

You don't really need a macro for this - you can do much the same using Word's mailmerge facility, combined with a SKIPIF field,
to generate a set of product descriptions for all products where Quantity > 0. The SKIPIF field would be coded as:
{SKIPIF {MERGEFIELD Quantity}< 1}
or
{SKIPIF «Quantity»< 1}
You could use '= 0' instead of '< 1', but the latter allows for the posibility that the Quantity is a calculated value and
someone's messed up one of the inputs, leading to negative values.

Note: The field brace pairs (ie '{ }') for the above examples are created via Ctrl-F9 - you can't simply type them or copy &
paste them from this message. Likwise, you can't type or copy & paste the chevrons (ie '« »') - they're part of the actual
mergefields, which you can insert from the mailmerge toolbar.


--
Cheers
macropod
[Microsoft MVP - Word]

HI,

I need some help building a macro.
In excel let's suppose that I have the following table:

Product Description Quantity Path to details
Product 1 0 \\server\product1_description.doc
Product 2 1 \\server\product2_description.doc
Product 3 2 \\server\product3_description.doc

Now, I want to have a button that when someone clicks on it, it will call a
macro that verifies the Quantity column and if the quantity is >=1 it will
generate a word document and merge the product description (path to details
column) of the products that have a quantity >=1.

I have no idea where to start... I can't find the write vba command to merge
multiple .doc documents.
Any help would be much appreciated!

By the way, I started the macro with this lines of code:

Set wordapp = CreateObject("word.Application")
wordapp.documents.Open "d:\test.doc"
wordapp.Visible = True

This will create the "Main" document. I don't know how I can merge other
.doc in this "Main" file.
Thanks in advanced.

Luis Ramos