By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,996 Members | 884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,996 IT Pros & Developers. It's quick & easy.

Shell to Word and load a specific document

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

dixie
Aug 16 '06 #1
Share this Question
Share on Google+
12 Replies


P: n/a

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

Aug 16 '06 #2

P: n/a
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
Aug 16 '06 #3

P: n/a
or possibly

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

Aug 16 '06 #4

P: n/a
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

Aug 16 '06 #5

P: n/a
"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

Aug 16 '06 #6

P: n/a
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"

Aug 16 '06 #7

P: n/a
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

Aug 16 '06 #8

P: n/a
"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

Aug 17 '06 #9

P: n/a
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


Aug 17 '06 #10

P: n/a
"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/
Aug 17 '06 #11

P: n/a
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


Aug 17 '06 #12

P: n/a
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



Aug 17 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.