Solved: VBA for automating header information

[JohnnyBravo]

OK, this one should be a lot easier to solve than my other thread.

In the following folder C:\Letters Sent\ I've got a growing list of subfolder each with the name of the hospital we deal with. So for example:
C:\Letters Sent\ABC Hospital\ I've got several generic word files. Let's say they specialize in Heart, Lung & Liver transplants.

Before sending them out, I navigate to that folder using Windows Explorer, select the files in question (Heart, Lung, & Liver), and open them up. Then I type the name of the hospital (in this case "ABC Hospital") in the header section and run my macro named "forms protect". And repeat this for each document.

I recorded a macro but I need some help. The macro I recorded works for 5 windows open in Word. How can i make it effective regardless of the number of windows open? And how can i automatically paste the name of the hospital? Could VBA get that ifnormation from the name of the folder? Or can it grab it from the letter that's already saved to that folder? This is essentially what you would see in Windows Explorer:

C:\Letters Sent\ABC Hospital\

ABC Hospital.doc
Sec II Ad Heart.doc
Sec IV Ad Lung.doc
Sec VII Ad Liver.doc

Here's what I'm dealing with thus far:

ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
Selection.EndKey Unit:=wdLine
Selection.Paste
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
ActiveDocument.Protect Password:="xxxxxx", NoReset:=False,
Type:=_ wdAllowOnlyFormFields
ActiveDocument.Save
ActiveDocument.Close
Windows(2).Activate

[fumei]

Hi JohnnyBravo. The following MUST have a reference to Microsoft Scripting Runtime to work. I ran this from the Letters Sent folder - from a separate document named DoStuff. It put the proper header in each file:

hospital name.doc
Sec II Ad Heart.doc
Sec IV Ad Lung.doc
Sec VII Ad Liver.doc

in six folders in about 3 seconds. So six folders, each folder having four files - the foldername.doc and the heart, lung and liver files. Total 24 files processed with proper headers in less than 3 seconds. Although the time you get will of course will depend on your processor and RAM, and the size of the files.
Sub MakeHeaders()
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fld2 As Scripting.Folder
Dim fil As Scripting.File
Dim strFolderName As String

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder("c:\letters sent")
For Each fld2 In fld.SubFolders
strFolderName = fld2.Name
For Each fil In fld2.Files
If Right(LCase(fil.ShortName), 3) = "doc" Then
' made Word invisible as who needs to
' see all the flashing of documents being
' opened briefly
Application.Visible = False
Documents.Open FileName:=fil.Path
With ActiveDocument
.Unprotect, Password:=""
.Sections(1) _
.Headers(wdHeaderFooterPrimary).Range _
.Text = strFolderName
.Protect Type:=wdAllowOnlyFormFields, _
Password:=""
.Save
.Close
End With
End If
Next
Next
Application.Visible = True
Set fld = Nothing
Set fso = Nothing
End Sub

I would like to point out that having "generic" files in each folder is not efficient. Why are you doing that? If they are generic, there should be one of each.

I have no idea why do are doing this from Windows Explorer. Is there a point to that? Having all those windows open sucks up resources....as you have found out.

[JohnnyBravo]

I would like to point out that having "generic" files in each folder is not efficient. Why are you doing that? If they are generic, there should be one of each.

I have no idea why do are doing this from Windows Explorer. Is there a point to that?

Yes there is a method to the madness but I'm willing to consider alternatives. You know by now that i'm extremely new to VBA, so i don't know if what I'm doing could be made more effective utilizing VBA. You asked for it, so here it is - hope your eyeballs don't glaze over. ;)

I am sending out nearly 100 RFIs to hospitals across the country. My supervisor has been giving me a list of these hospitals approx. 15 at a time

You know by now from my previous postings what I have been trying to do in Word. Well in addition to all that, I also have to prepare a physical binder with tabbed dividers for each of the transplants the hospitals specialize in. Essentially I have to copy onto a floppy (floppies) the exact same contents of the physical binder itself.

Using the example provided of the ficticious hosp. named "ABC Hospital" who specilaizes in Adult Heart, Adult Lung, & Adult Liver. There's a MS Word document that is the exactly same as the hardcopy in the binder for the sections "Adult Heart"; "Adult Lung"; "Adult Liver". I have two Windows Explorer open. One I use to navigate to the subfolder where all those so-called "generic" files are stored. Then I simply copy the specific organs that the hospital specialize in and then paste those word files into the respective folders. Got me so far...Have I lost ya?

I do this for all the hospitals on my list. Once all those files have been copied into the respective hospital folders, i go back to the top of my list again. Start with ABC Hospital.

- in Windows Explorer, highlight and press 'Enter' to open the word files,
- manually type in the name of the hospital
- run the macro i created for the forms password protect file. (the macro also saves and closes it after the password routine is done).

And I do this for as many transplant types that the hospital specializes in.

So, your question on why I'm doing this.

I suppose I could do a mail merge on the Adults Heart document - which will avoid me having to manually type in the name of the hospital in the header. So on my list, if I have 10 out of 15 hosp. that specialize in "Heart", I could convert my so-called "generic" Heart file into a mail merge form and merge all the hospitals that specialize in Heart. And repeat for each transplant type.

I have a few problems with that.

#1) We are not talkin about just 3 or 4 transplant type. There are about 12 in total. That means I have to read down on my paper list 12 times - one each for the transplant type. More eyeball reading = MORE time consuming activity and also more chances for error - leaving out an organ when it should be included, reading the wrong line on my paper sheet, etc.

#2) I have the mail merge splitter which was provided in my previous posting. Now I'm finding out that the split (individual) letters are not coming out properly w/ the top margin causing some quirks. So again - more time involved fixing that. Not a good idea when I've got a 100 of these to do. Then I would also have to rename each of the split documents Sec II - Heart.doc and move them to the respective folders (again probably using Windows Explorer).

#3) Once all the sections (in MS word) have been "customized" w/ the header information, I copy those files onto a floppy diskette. It's much easier in Windows Explorer to simply do a Ctrl-A; R-click and send to Floppy Drive since I already have windows explorer opened to that specific hospital subfolder. Know what I mean?

I've been averaging 17 RFI's packages every 3 days. There are several additional steps involved which I have not explained it because it really does not matter. I think it would be much more efficient to knock out one hospital at a time, copying the files over in the way I described. But again - I'm open to suggestions. I'm trying to do it in the manner that is THE LEAST error prone, but also trying to be efficient about it.

[JohnnyBravo]

Fumei, i'm getting an error message in VBA editor:

"Compile Error:
User-defined type not defined."

The part that is highlighted is:

Dim fso As Scripting.FileSystemObject

I suspect it has something to do with Microsoft Scripting Runtime. So i did a search for the "scrrun.dll" file and it is indeed located in my system folder.

[fumei]

Yes, but did you actually make a reference to it? In the VBE, Tools > References. The DLL may be on your system, but if it is not explicitly referenced it is just another DLL.

[JohnnyBravo]

Yes, but did you actually make a reference to it? In the VBE, Tools > References. The DLL may be on your system, but if it is not explicitly referenced it is just another DLL.

I surely did - it still won't work.

[fumei]

OK, just to make sure. You have the document this code is in. THAT document is highlighted in the VBE - so it is the active project. Under Tools > References, Microsoft Scripting Runtime is both visible near the top, AND is checked?

[JohnnyBravo]

OK, just to make sure. You have the document this code is in. THAT document is highlighted in the VBE - so it is the active project. Under Tools > References, Microsoft Scripting Runtime is both visible near the top, AND is checked?

Gerry, I just did a test run on my home PC. It works fine. When I return to work on Monday, I'll try it again. It's a very large company and they've configured alll their PCs with Win XP in a very anal retentive manner. My guess is that perhaps there is something about the security settings on that PC which is preventing the code from being effective.

I should add that the folder I"m trying to run the script on is located on the network drive - I don't see how that would make a difference, but I'm not expert on these matters. My PC at home is not networked, I did the test run on my hard drive.

[JohnnyBravo]

Moderators (or Admin), you can mark this one as solved - I can't go back and edit my original posting.

I found out that there is something about our network drive that prevents VBA scripts from running. I can, however, run it on my local hard drive just fine.

Gerry - I appreciate your help. Thanks.