Switch between drives

[aehan]

Hello everyone

I'm trying to do something that is quite ambitious for me, and I'm getting
nowhere fast. I wonder if anyone has the answer to this.

I'm building toolbars to allow users to open new documents from custom
templates. However, the templates could be on a shared drive or they could
be on the C drive, according to the machine being used.

I've written this (which doesn't work and gives an error), and have been
trying all sorts of workrounds which again don't work. If anyone can help
I'd be grateful and my headache would maybe go.

The code is:

Dim strNetworkPath As String
Dim strCPath As String

Sub OpenLetter()

strNetworkPath = "G:\Documents and Settings\"%USERNAME%"\Application
Data\Microsoft\Templates\My Templates\"
strCPath = "%USERPROFILE%"\Application Data\Microsoft\Templates\My Templates\"


' Opens document based on letter template

Dim strname As String

If Application(strNetworkPath).Found Then

strFile = strNetworkPath & Letter.dot
Else
strFile = strCPath & Letter.dot
End If

Documents.Add Template:=strFile, NewTemplate:=False, DocumentType:=0

End Sub

 

[Jay Freedman]

The problem is that VBA doesn't understand environment variables the way
you'd write them for command lines or batch files. You need to use the
Environ() function:

strNetworkPath = "G:\Documents and Settings\" & Environ("username")
&"\Application Data\Microsoft\Templates\My Templates\"

and for the other path, use Environ("userprofile") instead of %USERPROFILE%.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[aehan]

Hi Jay

Thank you for that. I've done as you advised, but now I get an error (when
the code accesses this statement

If Application(strNetworkPath).Found Then

The error message is "Wrong number of arguments or invalid property
assignment".

I'm more or less doing this by trial and error, and obviously not too well.
Thanks for your help so far, and if you can think of what I'm doing wrong
with the rest of the code I'd be really greatful (and less stressed!).

Thank you
Aehan

 

[Jay Freedman]

OK, I didn't read that far down after I found the first problem. You have
several more problems in the code you originally posted:

- The expression Application(strNetworkPath).Found is meaningless in VBA. If
you want to know whether a folder exists, you can use the Dir() function
with the proposed folder name as the first argument and the constant
vbDirectory as the second argument. If the folder exists, the return value
of the function is the folder name; if it doesn't exist, the return function
is the empty string ("").

- To do that test, you should not have the backslash at the end of the
folder name; instead, make it part of the file name when you append that to
the path.

- The file name (Letter.dot) needs to be enclosed in double quotes. The rule
is: literal strings are enclosed in quotes, variable names are not enclosed
in quotes.

- You had a Dim statement naming a variable strname that was never used, but
no Dim statement for the variable strFile that was used. To avoid this sort
of misstep, always include the Option Explicit statement at the beginning of
each module (see
http://www.word.mvps.org/FAQs/MacrosVBA/DeclareVariables.htm).

- Although you're trying to make sure the correct folder is used, you
haven't checked that the template actually exists in the folder. You could
use the Dir() function again, with the vbNormal constant in the second
argument, or you can simply set an On Error statement to trap the problem
and handle it. The following sample code shows how that can work.

Sub OpenLetter()
Dim strNetworkPath As String
Dim strCPath As String
Dim strFile As String

strNetworkPath = "G:\Documents and Settings\" & Environ("USERNAME") &
"\Application Data\Microsoft\Templates\My Templates"
strCPath = Environ("USERPROFILE") & "\Application
Data\Microsoft\Templates\My Templates"


' Opens document based on letter template


If Dir(strNetworkPath, vbDirectory) <> "" Then

strFile = strNetworkPath & "\Letter.dot"
Else
strFile = strCPath & "\Letter.dot"
End If

On Error GoTo BadTemplate
Documents.Add Template:=strFile, NewTemplate:=False, DocumentType:=0

Exit Sub

BadTemplate:
MsgBox "Couldn't find " & strFile
End Sub

 

[aehan]

Hi Jay

Thank you so much for helping me, I appreciate not only the fact that you
showed me what to do, but also that you explained where I had gone wrong -
that really helps.

Have a great Christmas.
Aehan