Relative File Path Name

[Steve C]

I am programming a Word 2003 form where a user will select a project name
from a combo box. The project names listed are the "formal" names (i.e.,
20089999 - ABC Development Project) used in official correspondence. Upon
clicking OK, the info is distributed into the opened document. That's
working fine.

In addition, we have project folders on our network P: drive that are
similar, but not identical to, the formal names. In the above example, the
P: drive version might be called 20089999 - ABC Dev Proj. The formal names
and the P: drive names, however, always begin with the same 8 digit project
number.

My goal is that when a user clicks OK on the form, I want the resulting
document to be saved automatically to that project's folder on our network P:
drive in a subfolder called Instructions. I need help in determining how to
create code that would use a relative path to find the 8 digit project number
on the P: drive without having to match the entire file name. Thanks for any
help you can provide!

 

[Karl E. Peterson]

I am programming a Word 2003 form where a user will select a project name
from a combo box. The project names listed are the "formal" names (i.e.,
20089999 - ABC Development Project) used in official correspondence. Upon
clicking OK, the info is distributed into the opened document. That's
working fine.

In addition, we have project folders on our network P: drive that are
similar, but not identical to, the formal names. In the above example, the
P: drive version might be called 20089999 - ABC Dev Proj. The formal names
and the P: drive names, however, always begin with the same 8 digit project
number.

My goal is that when a user clicks OK on the form, I want the resulting
document to be saved automatically to that project's folder on our network P:
drive in a subfolder called Instructions. I need help in determining how to
create code that would use a relative path to find the 8 digit project number
on the P: drive without having to match the entire file name. Thanks for any
help you can provide!

You'll want to become familiar with the Mid$() and Instr() functions.

 

[Steve C]

I'm familiar with those functions, but I'm hoping you might be able to
provide me with some sample code that uses them. Here's what I've been
unsuccessfully trying so far:

Sub SaveInstructions(ProjNum, DocName) ' called by Private Sub cmdOK_Click()

ChangeFileOpenDirectory _
"P:\ & ProjNum\..\Instructions\"
ActiveDocument.SaveAs FileName:= _
DocName, FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False

End Sub

Thanks,

Steve C

 

[Karl E. Peterson]

I'm familiar with those functions, but I'm hoping you might be able to
provide me with some sample code that uses them. Here's what I've been
unsuccessfully trying so far:

Sub SaveInstructions(ProjNum, DocName) ' called by Private Sub cmdOK_Click()

ChangeFileOpenDirectory _
"P:\ & ProjNum\..\Instructions\"
ActiveDocument.SaveAs FileName:= _
DocName, FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False

End Sub

Well, you're going to have to actually do a little coding there, and not hope Word
will automagically correlate things for you. Let's start with what you actually
know. Presumably, you have the "Project Number", right? And your task is to
transform that to a DocName? Actually, even that isn't clear. Re-reading below, it
sounds like you need to transform the ProjName into a FolderName. I have no idea
where you're hoping/trying to get the actual document name from. Nor do I know if
you are constrained by an existing set of destination folders, or if you're free to
create them as needed and if so where exactly the list of sanctioned project numbers
and related destination folders are stored, or...

Programming comes down to A) defining the problem, and B) determining what steps are
required to solve the problem. I don't think we've even gotten close to finishing
step A here.

 

[Steve C]

Karl,

Let’s try again. Thank you for your patience in trying to help:

1. The P: drive contains over 200 existing project folders that all begin
with an 8 digit number, i.e., 20089000 – ABC Office Building, 20087315 – GHI
Waterpark, 20070040 – DEF Restaurant, etc. These folder names are typically
abbreviated quite a bit in an effort to reduce long file paths on our
network. Each existing project folder on P: contain numerous subfolders,
including an Instructions subfolder.

2. The Word template I’m working on (called “Instructions to
Subcontractorsâ€) contains instructions for subcontractors who work on these
projects. When initiated, it opens a user form prompting the user to select
which project (from a combo box) the instructions are to be created for. The
project list in that combo box contains the full “formal†name of the
project. For example, 20089000 – ABC Office Building Site, Hotel and
Convention Center (compared to its P: drive folder name, 20089000 – ABC
Office Building). We use the formal name in the combo box because, once the
user clicks OK, that name is inserted into various places in the document to
make it a professional, legal document. Various other instructions in the
document are changed as appropriate, depending on the type of project.

3. Users have asked me, once they click OK, if the document can also be
saved automatically to the corresponding (and already existing) P: drive
project of the same number in the Instructions subfolder (it would be saved
as “Instructions to Subcontractors.docâ€). If the formal name of the project
and the P: drive project name were always identical, I could program that
with no problem.

But since they are not the same, my challenge is to get the programming to
find at least the project number on the P: drive, ignore whatever abbreviated
name follows it, then find the Instructions subfolder and save the document
to it.

I’ve programmed hundreds of Word and Excel templates to do things far more
complex than this, so I have tons of experience using functions and creating
variables to capture whatever information I need (i.e., the project number,
name, document name, etc). But a programming mastermind I’m not, so when I
get stuck, I often record macros and start experimenting with how I can
substitute variables into the resulting code to achieve what I need. That’s
what I sent you in my previous posting, and I admit it wasn’t pretty.

Does this help you better understand what I need to do? Thank you again for
your perseverance and efforts to help.

 

[Karl E. Peterson]

Step 1 will then be extracting the project number from the combobox entry. Do you
have any issues with that? Seems to me, it's as simple as:

ProjNum$ = Left$(Combo1.Text, 8)

Note I used a String there. No need to convert it to a number, since the
destination folder is also named using a String. So, now we know the "lookup" key
for the destination folder!

Step 2 becomes a question of *finding* the full name of the folder. Again, very
simple. Here's a quickie little function that'll help:

Public Function FindFolder(ByVal StartPath As String, ByVal StartsWith As String)
As String
Dim Path As String
Path = Dir$(StartPath & "*.*", vbDirectory)
Do While Len(Path)
If GetAttr(StartPath & Path) And vbDirectory Then
If InStr(1, Path, StartsWith, vbTextCompare) = 1 Then
' We found a match, return it!
Debug.Print "Match... "; Path
FindFolder = StartPath & Path
Exit Do
End If
Debug.Print "No Match... "; Path
End If
Path = Dir$()
Loop
End Function

You can try this in your immediate window, by typing something like:

?findfolder("p:\", "20070040")

That assumes, of course, that these folders are in the root. Adjust that StartPath
as necessary.

Make sense?

 

[Steve C]

Karl,

Thanks so much. I will digest this and work on it this coming week. I will
post back when I've had the chance to test it out.

 

[Steve C]

Karl,

I've successfully incorporated your function into my template and it is
working beautifully! Thank you again for your help, and especially for your
patience in trying to understand what I needed to do. I learned a lot thanks
to you!

 

[Karl E. Peterson]

Karl,

I've successfully incorporated your function into my template and it is
working beautifully! Thank you again for your help, and especially for your
patience in trying to understand what I needed to do. I learned a lot thanks
to you!

Hey, that's really nice to hear. Thanks.