DOCVARIABLE vs Bookmark

[Compass Rose]

Our company has a group of aboiut 50 documents that are used in administering
the projects we do. Each document is customized with the project name and
number. Every time a new project is started, one of the office staff manually
inserts the project name and number into the document. I would like to write
some code to automate the process.

Based on my very limited knowledge of VBA, I have come up with 2 options:
use DOCVARIABLEs or Bookmarks to place the text in the documents. Before I
get too far into it, I would like to know what are the pros and cons of each
option.

TIA
David

 

[Jonathan West]

Our company has a group of aboiut 50 documents that are used in
administering
the projects we do. Each document is customized with the project name and
number. Every time a new project is started, one of the office staff
manually
inserts the project name and number into the document. I would like to
write
some code to automate the process.

Based on my very limited knowledge of VBA, I have come up with 2 options:
use DOCVARIABLEs or Bookmarks to place the text in the documents. Before I
get too far into it, I would like to know what are the pros and cons of
each
option.

Hi David,

There is a further option you may want to consider - DOCPROPERTY fields
instead of DOCVARIABLE fields.

There is one potential key advantage of docproperties. If you use or may
intend in future to use a document management system like SharePoint,
iManage or Documentum, then having key metadata available in document
properties makes it easier for documents to be correctly indexed when
introduced to the system.

Now that is out of the way, I'll ask a question. Automated how? In other
words, by what means is the correct project name and number going to be
identified?

Also, will there ever be a need to update the project name or number once a
document has been created?

 

[Compass Rose]

Hi Jonathan

Considering that I would classify our decision makers as one step above IT
neaderthals, a document management system is most likely not in the cards!!

I was intending to start with Input Boxes for Project Name and Project
Number, then open each of the 50 documents one at a time, insert the
information into the document in the location of the DOCVARIABLE (or
Bookmark), Save As... to the appropriate project folder, and then close the
document. The name or number would not change again for those documents,
since they are specific to only that project.

Hope this provides the information you're looking for.

David

 

[Greg Maxey]

CR,

Jonathan is more than likely to come along with a much more refined
solution, but I got interested and thought I would give it a stab.
Bear in mind, this code has been subject to very limited testing and
would certainly benefit from enhancements.

I am assuming that you already have templates for each of the 50 forms
that you create for each project. For this example, I only have two
forms ProjForm1 and ProjForm2. The templates are located in a folder
called "C:\FormTemplates"

Each form template has entries like,

Project Name: {DocVariable "PName" } and Project Number:
{DocVariable "PNum"}

The field codes are toggled off and each form is saved in
C:\FormTemplates as

ProjForm1, ProjForm2, etc. (for more forms)

I also have a directory C:\Projects which is the root repository for
all existing projects.

Run this code:

Sub CreateProjectForms()
Dim PName As String
Dim PNum As String
Dim i As Long
PName = InputBox("Enter the project name:")
CPF_ReEntry:
PNum = InputBox("Enter the project number:")
Application.ScreenUpdating = False
On Error GoTo CPF_Error
'Make a sub-direcorty of C:\Projects to store the new project forms.
FileSystem.MkDir ("C:\Projects\Project " & PNum)
'Create a new document based on each of the numbered ProjForm templates
For i = 1 To 2
Documents.Add ("C:\FormTemplates\ProjForm" & i), , , False
With ActiveDocument
'Assign the variable values
.Variables("PName").Value = PName
.Variables("PNum").Value = PNum
'Display the date
.Fields.Update
'Save the document as a numbered form
.SaveAs FileName:="C:\Projects\Project " & PNum & "\FormTest " & i
& ".doc"
.Close
End With
Next i
Application.ScreenUpdating = True
Exit Sub
CPF_Error:
If Err.Number = 75 Then
If MsgBox("A Project " & PNum & " folder already exists." _
& " You must assign a new unique project number.", vbOKCancel,
"Invalid Project Number") = vbOK Then
Resume CPF_ReEntry
End If
End If
End Sub

This seems to work reasonable well.

 

[Compass Rose]

Thanks, Greg. Very simple, but effective.

A couple of questions:
1) Please explain 'CPF_ReEntry:' and 'CPF_Error:'
2) Why is ScreenUpdating turned off during the process?
3) How could the code be modified to read all of the documents in a
particular folder, perform the operation on all the files in that folder (one
at a time, of course)and then save them to the newly created folder using the
same file name?

David

 

[Greg Maxey]

David,

Answers:

1. This is error handling. If you attempt to create a project
directory that already exists then a Run Time error will occur. You
use error handling to trap the error and act on it accordingly (i.e.,
change the project number). See:
http://gregmaxey.mvps.org/Error_Handling_Basics.htm

2. From VBA Help:

The ScreenUpdating property controls most display changes on the
monitor while a procedure is running. When screen updating is turned
off, toolbars remain visible and Word still allows the procedure to
display or retrieve information using status bar prompts, input boxes,
dialog boxes, and message boxes. You can increase the speed of some
procedures by keeping screen updating turned off. You must set the
ScreenUpdating property to True when the procedure finishes or when it
stops after an error.

To tell you the truth I don't really understand what it does either. I
have no formal training in VBA and most of what I do is based on trial
and error and what I have seen others do.

3. Interesting challenge and is it not considered convential wisdom to
create new files based on old ones. That is what templates are for
;-). The tricky part was creating a new unique folder to save the
files in. Try:

Sub CreateProjectFormsFromOldProject()
Dim myFile As String
Dim PathToUse As String
Dim pNewFolder As String
Dim myDoc As Document
Dim pName As String
Dim pNum As String
'Get the folder containing the files
With Dialogs(wdDialogCopyFile)
If .Display <> 0 Then
PathToUse = .Directory
Else
MsgBox "Cancelled by User"
Exit Sub
End If
End With
'Clean up path string
If Left(PathToUse, 1) = Chr(34) Then
PathToUse = Mid(PathToUse, 2, Len(PathToUse) - 2)
End If
'Make a sub-directory to store the new project forms.
pNewFolder = PathToUse
'Strip "\" from end of path
pNewFolder = Left(pNewFolder, Len(pNewFolder) - 1)
'Strip existing project number from folder path
While Right(pNewFolder, 1) Like "#"
pNewFolder = Left(pNewFolder, Len(pNewFolder) - 1)
Wend
pName = InputBox("Enter the project name:")
CPF_ReEntry:
pNum = InputBox("Enter the project number:")
Application.ScreenUpdating = False
On Error GoTo CPF_Error
FileSystem.MkDir (pNewFolder & pNum)
myFile = Dir$(PathToUse & "*.doc")
While myFile <> ""
Set myDoc = Documents.Open(PathToUse & myFile)
With myDoc
'Assign the variable values
.Variables("pName").Value = pName
.Variables("pNum").Value = pNum
'Update the fields
.Fields.Update
'Save the document as a numbered form
.SaveAs FileName:=pNewFolder & pNum & "\" & myFile
.Close
End With
myFile = Dir$()
Wend
Application.ScreenUpdating = True
Exit Sub
CPF_Error:
If Err.Number = 75 Then
If MsgBox("A Project " & pNum & " folder already exists." _
& " You must assign a new unique project number.", _
vbOKCancel, "Invalid Project Number") = vbOK Then
pNum = InputBox("Enter the project number:")
Resume
End If
End If
End Sub

 

[Greg Maxey]

P.S.

The code assumes that you are selecting your files from one of the
previously numbered Project folders that we created in the first exercise.

 

[Compass Rose]

Thanks, Greg, this is great! I'll have some further tweaking to do, but you
have definitely set me in the right direction. If I have any additional
challenges, I'll be back.

Like you, I learn by trial and error and asking questions, but I'm well
behind you. How long ago did you start your learning of VBA?

David

 

[Greg Maxey]

David,

Glad I could help.

I think I first put my toe in the VBA pool about 4 years ago. It
definately has a deep end and I I have frequently been in way over my
head. Almost everytime one of the Senseis that frequent this group has
come by to throw me a life line. There are dents in the wall of my
little one room geo-bachelor pad and bits of hair and dried scalp lying
about but I enjoy the challenge of trying to put together something
that works.

That said, remember that the source of those "somethings" is a self
proclaimed amatuer and the testing is "hey it worked, Eureka" ;-)

 

[Lüko Willms]

Am Thu, 26 Oct 2006 17:08:20 UTC, schrieb "Greg Maxey"

With ActiveDocument
'Assign the variable values
.Variables("PName").Value = PName
.Variables("PNum").Value = PNum

I would prefer to use the BuiltInDocumentProperties, e.g. Title,
Comments, Keywords, etc.
Documents can refer to these by using fields. That is the standard
method of identifying documents, and it can be looked up by the user
via the standard MS-Word menus (File >> Properties). Word knows how to
print a document including the document properties.

Alternatively to Document.BuiltInDocumentProperties one might use
CustomDocumentProperties; but I have to admit that I have never tried
those, and I don't know how to refert to them via {FIELDS}...

As to the management of files and directories, I recommend to use
the FSO = FileSystem Object -- did you refer to that with your
statement:

FileSystem.MkDir ("C:\Projects\Project " & PNum)

the method to create files with the FSO is fso.CreateFolder

Dim fso, targetDirectory As Object

Set fso = CreateObject("Scripting.FileSystemObject")
If NOT fso.FolderExists("C:\Projects\Project " & PNum)
Set targetDirectory = fso.CreateFile("C:\Projects\Project " &
PNum)
Else
' Do something to find a new directory, or ask for overwriting
End If


Yours,
L.W.

 

[Lüko Willms]

Am Thu, 26 Oct 2006 23:43:02 UTC, schrieb Compass Rose

2) Why is ScreenUpdating turned off during the process?

Otherwise you would see a constant flicker of all 50 newly created
documents on the screen as they are created and saved.

Thanks to Greg Maxey for making me aware of that ... I was looking
for that for a current project without knowing about it...

If using it, one might consider to change System.Cursor =
wdCursorWait, and storing the previous cursor form in a variable and
restoring this at the end of the procedure:

Dim previousCursor As Long

previousCursor = System.Cursor
System.Cursor = wdCursorWait
Application.ScreenUpdating = False
' do something taking long time
Application.ScreenUpdating = True
System.Cursor = previousCursor

3) How could the code be modified to read all of the documents in a
particular folder, perform the operation on all the files in that folder (one
at a time, of course)and then save them to the newly created folder using the
same file name?

using the FSO, er, the FileSystemObject

Dim fso, sourceFolder, destinationFolder, myTemplate, templateFiles
_
As Object
Dim newDocument As Document
Dim newPath As String

Set fso = CreateObject("Scripting.FileSystemObject")
Set sourceFolder = fso.GetFolder(some_String_with_the_Path)
If NOT fso.FolderExists(some_String_with_destination_Path)
Set destinationFolder = _
fso.CreateFolder(some_String_with_destination_Path)
Else
Set destinationFolder = _
fso.GetFolder(some_String_with_destination_Path)
End If
newPath = destinationFolder.Path
Set templateFiles = sourceFolder.Files
For Each myTemplate IN templateFiles
IF UCase(fso.GetExtensionName(myTemplate.Path)) = "DOT" _
OR myTemplate.Type = "Word Template" Then
Application.Documents.Add _
Template := myTemplate.Path & myTemplate.Name
' not sure, if the Path property does not already
' contain the full name
newDocument = ActiveDocument
newDocument.BuiltInDocumentProperties(wdPropertyTitle) _
= newProjectName _
& " " & newDocument.BuiltInProperties(wdPropertyTitle)
' add more BuiltInDocumentProperties, CustomDocumentProperties,
' or Variables as needed
newDocument.SaveAs FileName := newPath _
& fso.GetBaseName(myTemplate.Name) & ".doc" _
, FileFormat := wdFormatDocument _
, AddToRecentFiles := False ' maybe...
NewDocument.Close
End If
Next aFile

' -----------------------

Just typed, not compiled or tested.

Check out the meaning of "Path" and "Name" in the FSO (btw, I think
that there is some "FullName" property of a file).


Have fun,
L.W.

 

[Lüko Willms]

Am Sat, 28 Oct 2006 08:05:46 UTC, schrieb "Lüko Willms"

Next aFile

This should, of course, read as

Next myTemplate

I had changed the variable name during the course of composing that
contribution and forgot to change the variable name on the last
line...


Yours,
L.W.

 

[Greg Maxey]

Lüko,

Thanks for your tips and suggestions. I agreed with Jonathan from the start
that DocProperties are a better choice, but I was lazy and variables are so
much easier to work with. Or so I thought.

You can easily add the custom properties with code like:

Sub ScratchMacro()
Dim PName As String
Dim PNum As String
PName = "The Project"
PNum = "1234"
With ActiveDocument
.CustomDocumentProperties.Add Name:="Project Name", LinkToContent:=False,
Value:=PName, _
Type:=msoPropertyTypeString
.CustomDocumentProperties.Add Name:="Project Number",
LinkToContent:=False, Value:=PNum, _
Type:=msoPropertyTypeString
End With
End Sub

And call them in the document with:

{ DOCPROPERTY "Project Name" }

 

[Greg Maxey]

Actually you could create the custom property once in the templates and then
set the value in each document opened:

Sub ScrathMacro()
Dim PName As String
PName = "The Project"
ActiveDocument.CustomDocumentProperties("Project Name").Value = PName
ActiveDocument.Fields.Update
End Sub

 

[Greg Maxey]

I suppose after reading your suggestions that I would use something like
this:

Sub CPFormsUsingFSOCode()
Const pTempDir = "E:\My Documents\Word\Word Documents\Word Tips\Batch
Folder\FormTemplates\"
Const pSaveDir = "E:\My Documents\Word\Word Documents\Word Tips\Batch
Folder\Projects\Project "
Dim pName As String
Dim pNum As String
Dim fso As Object
Dim sourceFolder As Object
Dim destinationFolder As Object
Dim myTemplate As Object
Dim templateFiles As Object
Dim oDoc As Document
Dim newPath As String
Dim curCursor As Long

Set fso = CreateObject("Scripting.FileSystemObject")
Set sourceFolder = fso.GetFolder(pTempDir)
'Get project data
pName = InputBox("Enter the project name:")
CPF_ReEntry:
pNum = InputBox("Enter the project number:")
'Create storage directory for new project files
On Error GoTo CPF_Error
Set destinationFolder = fso.CreateFolder(pSaveDir & pNum)
On Error GoTo 0

'This is code you would use if wanted create and use a folder that didn't
already exist
'or use it if it did.
'If Not fso.FolderExists(pSaveDir & PNum) Then
' Set destinationFolder = fso.CreateFolder(pSaveDir & PNum)
' Else
' Set destinationFolder = fso.GetFolder(pSaveDir & PNum)
'End If

'Minimize screen flicker
curCursor = System.Cursor
System.Cursor = wdCursorWait
Application.ScreenUpdating = False
'Create batch of forms
newPath = destinationFolder.Path & "\"
Set templateFiles = sourceFolder.Files
For Each myTemplate In templateFiles
If UCase(fso.GetExtensionName(myTemplate.Path)) = "DOT" _
Or myTemplate.Type = "Word Template" Then
Application.Documents.Add Template:=myTemplate.Path
Set oDoc = ActiveDocument
With oDoc
.CustomDocumentProperties("Project Name").Value = pName
.CustomDocumentProperties("Project Number").Value = pNum
.Fields.Update
.SaveAs FileName:=newPath _
& fso.GetBaseName(myTemplate.Name) & ".doc" _
, FileFormat:=wdFormatDocument _
, AddToRecentFiles:=False
.Close
End With
End If
Next myTemplate
Set fso = Nothing
Set sourceFolder = Nothing
Set destinationFolder = Nothing
Set myTemplate = Nothing
Set templateFiles = Nothing
Set oDoc = Nothing
'Restore visuals
Application.ScreenUpdating = True
System.Cursor = curCursor
Exit Sub
CPF_Error:
If Err.Number = 58 Then
If MsgBox("A Project " & pNum & " folder already exists." _
& " You must assign a new unique project number.", _
vbOKCancel, "Invalid Project Number") = vbOK Then
Resume CPF_ReEntry
End If
End If
End Sub

 

[Lüko Willms]

And call them in the document with:

{ DOCPROPERTY "Project Name" }

what I just found out by testing it myself.

One can add custom document properties manually using the Document
Properties menu dialog. Word has a lot of valid suggestions for
additional properties in that dialog.

BTW, the built-in document property "Subject" offers itself best for
the Project name, I think.

As to your suggestion to include those document properties in the
original template, I agree wholeheartily.

Finally, I wonder if the whole thing could be simplified a lot, by
setting "LinkToContent" to True, and using a master document for the
whole project, of which the "group of about 50 documents that are used
in administering the projects we do" (orginal post of 'Compass Rose')
are sub-documents. But I am not sure if I have understood the
explanations of "LinkToContent" and "LinkSource" correctly; it talks
about "Container", not sub- and master-documents.

Besides, in:

.CustomDocumentProperties.Add Name:="Project Number",
LinkToContent:=False, Value:=PNum, _
Type:=msoPropertyTypeString

I would use "Type:=msoPropertyTypeNumber". Well, when using a
numeric type, one would better provide a form for the entry of both
the name and the number instead of an InputBox.


Yours,
L.W.

 

[Greg Maxey]

The basic opinion of most of the Word MVPs is to avoid Master Documents at
all cost. I personnally have never used them so I haven't experienced
problems. The adage is:

A master document is a document that is either corrupt or about to be
corrupted.

Or something along those lines.

I may refine my code a bit with your other suggestion.

Thanks

 

[Peter Jamieson]

Finally, I wonder if the whole thing could be simplified a lot, by

setting "LinkToContent" to True

I would avoid that if I were you, unless you can verify that everything you
ever want to do with your document properties will work. Linking to content
involves making references to bookmarks which can easily be destroyed, and
is generally much more likely to break than using simple properties (which
have limitations themselves - for example you may find that they are limited
to 127/255 characters and so on).

IMO the advantages of using Document Properties are potentially large as
Jonathan West hinted - you don't even need a document management system to
make use of them. For example, even in Windows Explorer you can expose
/some/ properties in the "Details" view. If you are willing to write a
Windows Shell Extension Column Provider you could expose any property you
wanted. In Outlook, you used to be able to expose the document properties of
any document that conformed to the OLE Structured File System stnadards that
the office programs use to store their properties. However, over the years
MS and others have implemented "properties" in so many ways in different
products that it is not easy to tell how the various sets of properties will
interact (e.g. older Office document properties were implemented differently
and are incompatible, Office documents use the OLE Structured fiel system,
but the NTFS file system has its own property mechanism, as does (or did)
Exchange's M: drive file system, SharePoint, and so on.

Peter Jamieson

 

[Lüko Willms]

Am Sat, 28 Oct 2006 08:05:46 UTC, schrieb "Lüko Willms"

using the FSO, er, the FileSystemObject

for Word (and other MS-Office applications) I just found the
FileSearch-Object, which is at least good enough to loop thru a
directory and get e.g. all Word templates.


Yours,
L.W.