Shell to Word and load a specific document

[Dixie]

Is there a way to shell to Microsoft Word from Access and load a specific
template - using VBA?

dixie

[pietlinden]

Dixie wrote:

Is there a way to shell to Microsoft Word from Access and load a specific
template - using VBA?

dixie

Like this:
http://www.mvps.org/access/modules/mdl0043.htm

[Tanis]

Or, you could use the simple Shell command

Call Shell("C:\program files\Microsoft Office\Office11\winword.exe
h:\templates\templatetest.dot", 1)

Just change the pathname to suit.

Dixie wrote:

Is there a way to shell to Microsoft Word from Access and load a specific
template - using VBA?

dixie

[Ron2006]

or possibly

Application.followhyperlink "h:\templates\templatetest.dot"

[Dixie]

I am using a template called "Rewards letter.dot".

When I use thecfollowing code:

Call Shell("C:\program files\Microsoft Office\Office\winword.exe
f:\templates\(Rewards Letter.dot)", 1)

Word opens and then says "The file rewards.doc is not available."
How do I handle the two word template name? Is there some way of doing it
with different quotation marks?

dixie
"Tanis" <dn*****@accuride.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...

Or, you could use the simple Shell command

Call Shell("C:\program files\Microsoft Office\Office11\winword.exe
h:\templates\templatetest.dot", 1)

Just change the pathname to suit.

Dixie wrote:

>Is there a way to shell to Microsoft Word from Access and load a specific
template - using VBA?

dixie

[Bob Quintal]

"Dixie" <di***@dogmail.comwrote in
news:12*************@corp.supernews.com:

I am using a template called "Rewards letter.dot".

When I use thecfollowing code:

Call Shell("C:\program files\Microsoft
Office\Office\winword.exe f:\templates\(Rewards Letter.dot)",
1)

Word opens and then says "The file rewards.doc is not
available." How do I handle the two word template name? Is
there some way of doing it with different quotation marks?

dixie

Yes.

what you want in the command is "C:\program files\Microsoft
Office\Office\winword.exe" "f:\templates\Rewards Letter.dot"

To get that, you
Call Shell("""C:\program files\Microsoft Office\Office
\winword.exe""
""f:\templates\Rewards Letter.dot""", 1)

The rule is to use two doublequotes to embed one doublequote in
the string. Then quote the entire string as usual.

"Tanis" <dn*****@accuride.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...

>Or, you could use the simple Shell command

Call Shell("C:\program files\Microsoft
Office\Office11\winword.exe h:\templates\templatetest.dot",
1)

Just change the pathname to suit.

Dixie wrote:

>>Is there a way to shell to Microsoft Word from Access and
load a specific template - using VBA?

dixie

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Dixie]

I haven't seen this one before, but it works. The problem I see is that the
window that I am running the Access application in mimimises to the task bar
as soon as Word opens, then when I close word, it does not return to that
window automatically. If there was a way or getting the active Access
window back when you closed the Word document, this would be good.

dixie

"Ron2006" <ro******@hotmail.comwrote in message
news:11**********************@75g2000cwc.googlegro ups.com...

or possibly

Application.followhyperlink "h:\templates\templatetest.dot"

[Dixie]

OK, that works fine. Now, I need to make this generic. I need to
substitute some DLookups for the path to Word and for the path to the
template. I have used this sort of stuff before, but not in the Call Shell
context.

This is sort of what I am looking to do:

Call Shell("DLookup("[LetterLabel]", "tblSetup", "ControlID=152") &" " &
CurrentProject.Path & "\Templates\" & DLookup("[Letter]", "tblLetters",
"ControlID=49") & ".dot")", 1)

Needless to say, it is not working and I get an Expected list separator or )
error. Again, I feel this should work, but I am tangled in quotations
again.

The DLookup on tblSetup returns the full path to winword.exe and the DLookup
on tblLetters gives me the basic letter template name without the .dot
extension which is added in the code line.

Please?

dixie

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...

"Dixie" <di***@dogmail.comwrote in
news:12*************@corp.supernews.com:

>I am using a template called "Rewards letter.dot".

When I use thecfollowing code:

Call Shell("C:\program files\Microsoft
Office\Office\winword.exe f:\templates\(Rewards Letter.dot)",
1)

Word opens and then says "The file rewards.doc is not
available." How do I handle the two word template name? Is
there some way of doing it with different quotation marks?

dixie

Yes.

what you want in the command is "C:\program files\Microsoft
Office\Office\winword.exe" "f:\templates\Rewards Letter.dot"

To get that, you
Call Shell("""C:\program files\Microsoft Office\Office
\winword.exe""
""f:\templates\Rewards Letter.dot""", 1)

The rule is to use two doublequotes to embed one doublequote in
the string. Then quote the entire string as usual.

>"Tanis" <dn*****@accuride.comwrote in message
news:11*********************@b28g2000cwb.googlegr oups.com...

>>Or, you could use the simple Shell command

Call Shell("C:\program files\Microsoft
Office\Office11\winword.exe h:\templates\templatetest.dot",
1)

Just change the pathname to suit.

Dixie wrote:
Is there a way to shell to Microsoft Word from Access and
load a specific template - using VBA?

dixie

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

"Dixie" <di***@dogmail.comwrote in
news:12*************@corp.supernews.com:

OK, that works fine. Now, I need to make this generic. I
need to substitute some DLookups for the path to Word and for
the path to the template. I have used this sort of stuff
before, but not in the Call Shell context.

This is sort of what I am looking to do:

Call Shell("DLookup("[LetterLabel]", "tblSetup",
"ControlID=152") &" " & CurrentProject.Path & "\Templates\" &
DLookup("[Letter]", "tblLetters", "ControlID=49") & ".dot")",
1)

Needless to say, it is not working and I get an Expected list
separator or ) error. Again, I feel this should work, but I
am tangled in quotations again.

The DLookup on tblSetup returns the full path to winword.exe
and the DLookup on tblLetters gives me the basic letter
template name without the .dot extension which is added in the
code line.

Please?

Divide and conquer!

The first thing to do is move the dlookups to variables. Then
examine the contents of those variables after they have been
set. Also move the multiple quotes to a symbolic constant.

Dim filePath as string, filename as string
Const dQt as string = """"

filepath = DLookup("[LetterLabel]", "tblSetup", "ControlID=152")
filename = DLookup("[Letter]", "tblLetters", "ControlID=49")

debug.print filepath, filename

'the resulting code is a lot easier to debug.
call shell(dQt & filepath & dQt _
& " " & dQt & filename & ".dot" & dQt _
& ")", 1)
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Dixie]

Call Shell(DLookup("[LetterLabel]", "tblSetup", "ControlID=152") & " " &
CurrentProject.Path & "\Templates\" & DLookup("[Letter]", "tblLetters",
"ControlID=49") & ".dot", 1)

I've just discovered that the above code works perfectly correctly if the
template name in the Letter field of tblLetters is only one word. If there
is a space in it, then it doesn't work. So the problem with the above code
is that the 2nd DLookup returns a space in the name of the template and it
can't work. That suggests to me some extra inverted commas for the second
DLookup only, but I can't seem to work out how to do it.

I have also noticed that if I put inverted commas around the actuall
template name in tblLetters such that it is "Commendation Letter" in the
table, it works fine. I don't really want to do it that way however if I
can solve it with some inverted commas in the code.

dixie

"Dixie" <di***@dogmail.comwrote in message
news:12*************@corp.supernews.com...

OK, that works fine. Now, I need to make this generic. I need to
substitute some DLookups for the path to Word and for the path to the
template. I have used this sort of stuff before, but not in the Call
Shell context.

This is sort of what I am looking to do:

Call Shell("DLookup("[LetterLabel]", "tblSetup", "ControlID=152") &" " &
CurrentProject.Path & "\Templates\" & DLookup("[Letter]", "tblLetters",
"ControlID=49") & ".dot")", 1)

Needless to say, it is not working and I get an Expected list separator
or ) error. Again, I feel this should work, but I am tangled in
quotations again.

The DLookup on tblSetup returns the full path to winword.exe and the
DLookup on tblLetters gives me the basic letter template name without the
.dot extension which is added in the code line.

Please?

dixie

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...

>"Dixie" <di***@dogmail.comwrote in
news:12*************@corp.supernews.com:

>>I am using a template called "Rewards letter.dot".

When I use thecfollowing code:

Call Shell("C:\program files\Microsoft
Office\Office\winword.exe f:\templates\(Rewards Letter.dot)",
1)

Word opens and then says "The file rewards.doc is not
available." How do I handle the two word template name? Is
there some way of doing it with different quotation marks?

dixie

Yes.

what you want in the command is "C:\program files\Microsoft
Office\Office\winword.exe" "f:\templates\Rewards Letter.dot"

To get that, you
Call Shell("""C:\program files\Microsoft Office\Office
\winword.exe""
""f:\templates\Rewards Letter.dot""", 1)

The rule is to use two doublequotes to embed one doublequote in
the string. Then quote the entire string as usual.

>>"Tanis" <dn*****@accuride.comwrote in message
news:11*********************@b28g2000cwb.googleg roups.com...
Or, you could use the simple Shell command

Call Shell("C:\program files\Microsoft
Office\Office11\winword.exe h:\templates\templatetest.dot",
1)

Just change the pathname to suit.

Dixie wrote:
Is there a way to shell to Microsoft Word from Access and
load a specific template - using VBA?
>
dixie

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[David W. Fenton]

"Dixie" <di***@dogmail.comwrote in
news:12*************@corp.supernews.com:

I haven't seen this one before, but it works. The problem I see
is that the window that I am running the Access application in
mimimises to the task bar as soon as Word opens, then when I close
word, it does not return to that window automatically. If there
was a way or getting the active Access window back when you closed
the Word document, this would be good.

Look up ShellAndWait on mvps.org/access.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Terry Kreft]

You've dropped your embedded double quotes again.

It should be

Call Shell(DLookup("[LetterLabel]", "tblSetup", "ControlID=152") & " """ &
CurrentProject.Path & "\Templates\" & DLookup("[Letter]", "tblLetters",
"ControlID=49") & ".dot""", 1)

or if you look at Bob Quintal's post where he starts with the words "Divide
and conquer!", he illustrates the way a programmer would approach this in
order to break the problem down into manageable bits.

--

Terry Kreft
"Dixie" <di***@dogmail.comwrote in message
news:12*************@corp.supernews.com...

Call Shell(DLookup("[LetterLabel]", "tblSetup", "ControlID=152") & " " &
CurrentProject.Path & "\Templates\" & DLookup("[Letter]", "tblLetters",
"ControlID=49") & ".dot", 1)

I've just discovered that the above code works perfectly correctly if the
template name in the Letter field of tblLetters is only one word. If

there

is a space in it, then it doesn't work. So the problem with the above

code

is that the 2nd DLookup returns a space in the name of the template and it
can't work. That suggests to me some extra inverted commas for the second
DLookup only, but I can't seem to work out how to do it.

I have also noticed that if I put inverted commas around the actuall
template name in tblLetters such that it is "Commendation Letter" in the
table, it works fine. I don't really want to do it that way however if I
can solve it with some inverted commas in the code.

dixie

"Dixie" <di***@dogmail.comwrote in message
news:12*************@corp.supernews.com...

OK, that works fine. Now, I need to make this generic. I need to
substitute some DLookups for the path to Word and for the path to the
template. I have used this sort of stuff before, but not in the Call
Shell context.

This is sort of what I am looking to do:

Call Shell("DLookup("[LetterLabel]", "tblSetup", "ControlID=152") &" " &
CurrentProject.Path & "\Templates\" & DLookup("[Letter]", "tblLetters",
"ControlID=49") & ".dot")", 1)

Needless to say, it is not working and I get an Expected list separator
or ) error. Again, I feel this should work, but I am tangled in
quotations again.

The DLookup on tblSetup returns the full path to winword.exe and the
DLookup on tblLetters gives me the basic letter template name without

the

.dot extension which is added in the code line.

Please?

dixie

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...

"Dixie" <di***@dogmail.comwrote in
news:12*************@corp.supernews.com:

I am using a template called "Rewards letter.dot".

When I use thecfollowing code:

Call Shell("C:\program files\Microsoft
Office\Office\winword.exe f:\templates\(Rewards Letter.dot)",
1)

Word opens and then says "The file rewards.doc is not
available." How do I handle the two word template name? Is
there some way of doing it with different quotation marks?

dixie

Yes.

what you want in the command is "C:\program files\Microsoft
Office\Office\winword.exe" "f:\templates\Rewards Letter.dot"

To get that, you
Call Shell("""C:\program files\Microsoft Office\Office
\winword.exe""
""f:\templates\Rewards Letter.dot""", 1)

The rule is to use two doublequotes to embed one doublequote in
the string. Then quote the entire string as usual.
"Tanis" <dn*****@accuride.comwrote in message
news:11*********************@b28g2000cwb.googlegr oups.com...
Or, you could use the simple Shell command

Call Shell("C:\program files\Microsoft
Office\Office11\winword.exe h:\templates\templatetest.dot",
1)

Just change the pathname to suit.

Dixie wrote:
Is there a way to shell to Microsoft Word from Access and
load a specific template - using VBA?

dixie



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Dixie]

I actually used Bob Quintal's approach. It returned the correct filepath
and filename in the Immediate window, but it the entire expression still did
not locate the file when opening Word. It was giving an error message "The
file Commendation Letter.dot) is not available" (in other words, it was
appending the parenthesis onto the filename.) I already knew I had a
problem with quotation marks in the second DLookup and was working on that
yesterday before I had to go out.

Thankyou everyone who has helped me. The last expression from Terry was
what I was looking for and I DO know I need to brush up on my use of
multiple quotation marks in such expressions. My problem is they crop up so
infrequently.

dixie

"Terry Kreft" <te*********@mps.co.ukwrote in message
news:Xt******************************@eclipse.net. uk...

You've dropped your embedded double quotes again.

It should be

Call Shell(DLookup("[LetterLabel]", "tblSetup", "ControlID=152") & " """ &
CurrentProject.Path & "\Templates\" & DLookup("[Letter]", "tblLetters",
"ControlID=49") & ".dot""", 1)

or if you look at Bob Quintal's post where he starts with the words
"Divide
and conquer!", he illustrates the way a programmer would approach this in
order to break the problem down into manageable bits.

--

Terry Kreft
"Dixie" <di***@dogmail.comwrote in message
news:12*************@corp.supernews.com...

>Call Shell(DLookup("[LetterLabel]", "tblSetup", "ControlID=152") & " " &
CurrentProject.Path & "\Templates\" & DLookup("[Letter]", "tblLetters",
"ControlID=49") & ".dot", 1)

I've just discovered that the above code works perfectly correctly if the
template name in the Letter field of tblLetters is only one word. If

there

>is a space in it, then it doesn't work. So the problem with the above

code

>is that the 2nd DLookup returns a space in the name of the template and
it
can't work. That suggests to me some extra inverted commas for the
second
DLookup only, but I can't seem to work out how to do it.

I have also noticed that if I put inverted commas around the actuall
template name in tblLetters such that it is "Commendation Letter" in the
table, it works fine. I don't really want to do it that way however if I
can solve it with some inverted commas in the code.

dixie

"Dixie" <di***@dogmail.comwrote in message
news:12*************@corp.supernews.com...

OK, that works fine. Now, I need to make this generic. I need to
substitute some DLookups for the path to Word and for the path to the
template. I have used this sort of stuff before, but not in the Call
Shell context.

This is sort of what I am looking to do:

Call Shell("DLookup("[LetterLabel]", "tblSetup", "ControlID=152") &" "
&
CurrentProject.Path & "\Templates\" & DLookup("[Letter]", "tblLetters",
"ControlID=49") & ".dot")", 1)

Needless to say, it is not working and I get an Expected list separator
or ) error. Again, I feel this should work, but I am tangled in
quotations again.

The DLookup on tblSetup returns the full path to winword.exe and the
DLookup on tblLetters gives me the basic letter template name without

the

.dot extension which is added in the code line.

Please?

dixie

"Bob Quintal" <rq******@sPAmpatico.cawrote in message
news:Xn**********************@66.150.105.47...
"Dixie" <di***@dogmail.comwrote in
news:12*************@corp.supernews.com:

I am using a template called "Rewards letter.dot".

When I use thecfollowing code:

Call Shell("C:\program files\Microsoft
Office\Office\winword.exe f:\templates\(Rewards Letter.dot)",
1)

Word opens and then says "The file rewards.doc is not
available." How do I handle the two word template name? Is
there some way of doing it with different quotation marks?

dixie

Yes.

what you want in the command is "C:\program files\Microsoft
Office\Office\winword.exe" "f:\templates\Rewards Letter.dot"

To get that, you
Call Shell("""C:\program files\Microsoft Office\Office
\winword.exe""
""f:\templates\Rewards Letter.dot""", 1)

The rule is to use two doublequotes to embed one doublequote in
the string. Then quote the entire string as usual.
"Tanis" <dn*****@accuride.comwrote in message
news:11*********************@b28g2000cwb.googleg roups.com...
Or, you could use the simple Shell command

Call Shell("C:\program files\Microsoft
Office\Office11\winword.exe h:\templates\templatetest.dot",
1)

Just change the pathname to suit.

Dixie wrote:
Is there a way to shell to Microsoft Word from Access and
load a specific template - using VBA?
>
dixie



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com