Running Word (and Excel) code from within Excel, to process both file types

[IanKR]

I'm running Windows XP, Excel 2003 and Word 2003.

I have written two sets of code - one to be run from Excel, stored in
modules in an Excel workbook, and another that is stored in modules in a
Word document. Each set basically does the same thing:

- prompts the user for a folder on a network share;
- searches that folder (and its sub-folders, if specified) for Excel
workbooks / Word docs (as appropriate);
- opens each file in sequence (only if a password is not required to open);
- checks headers and footers (for each worksheet and chart sheet in Excel;
for each section in Word) for existing text;
- edits existing header/footer text or adds header/footer where none exists
as appropriate, and under certain circumstances;
- saves and closes the workbook / document, if changed;
- adds a hyperlinked filename and path for each file checked to a listing in
a worksheet in the Excel code workbook (for the Excel files) / on the page
in the Word code document (for the Word files) with a comment as to whether
the file was changed;
- moves onto the next file;
- at the end, each set of code shows a summary message box with the total
files checked etc.

Each set of code runs fine from within Excel / Word, but ideally what I'd
like to do is to run one set of code from Excel, and have this check all
Excel and Word files in the specified location, do the editing as necessary,
and provide a full list on the Excel worksheet. This will mean running the
Word VBA code that manipulates the Word documents from within Excel. I know
that this is possible using early binding (preferable - I've seen an example
here http://word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm ).

My queries:

1. Once I've Dim'd the Word objects at the top as per:

Dim oWord As Word.Application
Dim oDoc As Word.Document

(plus any other string / Boolean etc references that I use in the Word
code), can I literally just copy the Word code into the Excel module?

2. A I am opening more than one Word doc in sequence, would it be preferable
to open a new instance of Word app for each Word doc that I check, or close
just the document each time and keep an empty Word app running ready for the
next Word doc?

3. Also, I know that for early binding I need to set a reference to MS Word
11.0 Word Object Library in Excel's VBE via Tools | References. Is this a
setting that is "saved" with the workbook containing the code (I want to
distribute it for others to use), or will each user have to set this
reference? I'd rather keep users well clear of the VBE. (If necessary, can
this setting be made via VBA?)

Grateful for any other general advice. I've not posted any code, but I could
do this if it helps. As I say, each set runs perfectly well in its own app,
checking for its own type of file, I'd just rather push it all into Excel.
In both cases I'm using Application.Filesearch to search for the files to
process. As I see it, there would be one instance of Application.Filesearch
and this would then branch to the Excel code to process the Excel files, or
to the Word code for the Word files, but for there to just the one
list/report for all the files, and for this to be on an Excel worksheet.

Many thanks in advance.

Ian

 

[Peter T]

My queries:

1. Once I've Dim'd the Word objects at the top as per:

Dim oWord As Word.Application
Dim oDoc As Word.Document

(plus any other string / Boolean etc references that I use in the Word
code), can I literally just copy the Word code into the Excel module?

a) Fully qualify all "implicit" object references back to the reference to
the Word application.
b) Best to fully declare Word object declarations, particularly "Range"
which of could easily get confused

Instead of -
Dim oDoc As Document
Set oDoc = ActiveDocument ' implicit
do -
Dim oDoc As Word.Document
Set oDoc = oWord.ActiveDocument ' qualified

2. A I am opening more than one Word doc in sequence, would it be
preferable to open a new instance of Word app for each Word doc that I
check, or close just the document each time and keep an empty Word app
running ready for the next Word doc?

If you are about to work on a new document stick with the same instance. If
not sure quit the instance, after ensuring you have released and object
references.

3. Also, I know that for early binding I need to set a reference to MS
Word 11.0 Word Object Library in Excel's VBE via Tools | References. Is
this a setting that is "saved" with the workbook containing the code (I
want to distribute it for others to use), or will each user have to set
this reference? I'd rather keep users well clear of the VBE. (If
necessary, can this setting be made via VBA?)

The reference will be saved. If there is any possibility a user with an
earlier version of Office will use your file you will either need to convert
to Late-Binding, or ensure the reference is always saved with the lowest
version of any user. Keep in mind the reference might get resaved and
changed a newer version if the file is exchanged between users.

I didn't follow what you wrote about FileSearch.

Regards,
Peter T

 

[IanKR]

My queries:

a) Fully qualify all "implicit" object references back to the reference to
the Word application.
b) Best to fully declare Word object declarations, particularly "Range"
which of could easily get confused

Instead of -
Dim oDoc As Document
Set oDoc = ActiveDocument ' implicit
do -
Dim oDoc As Word.Document
Set oDoc = oWord.ActiveDocument ' qualified

Thanks - understood.

If you are about to work on a new document stick with the same instance.
If not sure quit the instance, after ensuring you have released and object
references.

OK. I'll probably keep the same instance throughout and then close it at the
end.

The reference will be saved. If there is any possibility a user with an
earlier version of Office will use your file you will either need to
convert to Late-Binding, or ensure the reference is always saved with the
lowest version of any user. Keep in mind the reference might get resaved
and changed a newer version if the file is exchanged between users.

That's a relief!
We're all using Office 2003, so there's no issue here.

I didn't follow what you wrote about FileSearch.

Sorry, I rather hurried this bit. My point was that at the moment, in the
Excel code I do an Application.FileSearch and look just for .xls files and
process those. Then when I run the Word code (in MS Word) I also do an
Application.FileSearch and look just for .doc files, and process those. In
my unified code running in MS Excel I will do an Application.FileSearch and
look for .xls AND .doc files .This is possible using the .FileType method to
choose one, and then the .FileTypes.Add method to choose the second one.
E.g.:

Set fs = Application.FileSearch

With fs
.LookIn = strPathref
.FileType = msoFileTypeExcelWorkbooks ' will search for Excel files
.FileTypes.Add msoFileTypeWordDocuments 'will also search for Word
files
If .Execute > 0 Then 'if any files are found
For i = 1 To .FoundFiles.Count
If .FoundFiles(i).Creator = wdCreatorCode Then

'run Word VB code to process the .Foundfiles(i) Word file

End If

If .FoundFiles(i).Creator = xlCreatorCode Then

'run Excel VB code to process the .FoundFiles(i) Excel file

End If
Next i
Else
MsgBox "There were no Excel or Word files found."
End If
End With


Many thanks for your help, Peter.

Ian