Trigger deletion of Excel sheets based on keywords in Word

[JohnnyBravo]

We are sending out almost 100 RFIs to 100 hospitals across the country. The package consists of:

1) cover letter. (more or less a form letter) which gives directions on how to fill out the enclosed documents, etc. Everything revolves around the organ transplant type the hospital specializes in. There are 14 in total. I won?t list them all ? but here are a few examples:

-- Adult Heart
-- Adult Liver
-- Adult Liver-Kidney
-- Pediatric Autologous
-- Pediatric Allogeneic

This does not mean the hospital specializes in all 14 different types ? often it?s a combination of 4 or 5 different types. While some hospitals only specialize in one.

There is also a general excel file which the hospitals are required to input information. This Excel file has a sheet for just almost every one of those transplant types mentioned except for a couple of exceptions. The first sheet in the workbook is the instructions sheet, which every hospital will need to get.

2) I have to send this excel file to the hospital ?customized?. In essence, I delete all the other sheets that don?t pertain to that hospital. So let?s take a fictitious example of Kramer Hospital. Kramer Hospital specializes in only Adult Heart and Liver. Obviously when I send the file, I want to send only the first instruction sheet and the sheets for heart and liver.

So based on what I call ?KEY WORDS? (or the organ transplant type) listed within a bookmark RANGE called "transplant types" end of paragraph 1 of cover letter, I want Word to trigger Excel, then open up the Excel file, then delete all the sheets in that workbook that do not pertain to that hospitals specialty; then prompt me to do a Save As where I can choose the folder (path) of my own choosing.

Let's say the excel file is here:

C:\RFI\Excel Sheets\Organ transplants.xls

There are sheets (tabs) for ?Adult Heart? "Adult Liver??. Etc.

[Dave]

I think you should hire a professional consultant when your messing with peoples' lives. People are generous and your project is interesting. Maybe the people that hire you to computurize their records should reconsider their hiring/contracting practises before someone gets hurt and they become negligent. A "glitch" could cost someone their life. Their is alot of great free learning to be had here but if you are trying to pass yourself off as knowlegable perhaps that should stop before someone gets hurt? Dave

[Killian]

Wow Dave, that's a bit harsh!
I mean, I'm sure the Excel sheets aren't the instructions for how to do the transplant... don't they have people that already know how to do that? Surgeons?

An RFI, if I'm not mistaken, is a Request for Information. JB's probably sending out a questionnaire or somesuch thing about stuff related to some aspect of their transplant work. You know like:
"Dear Dr whoever, I understand you do "Pediatric Autologous" transplants. Would you be interested in viewing our range of scalpels designed especially for "Pediatric Autologous" transplants? (see attached Excel sheet for prices) Or do you prefer to use a spoon?"

So, JonnyBravo, provided no-one's going to die because the VBA code is wrong (what a strange healthcare system that would be) here's an idea:
[VBA]Sub SomeSubName()

'In VBE>Tools>References, add a reference to the
'Microsoft Excel Object Library

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim para As Paragraph
Dim blnDeleteThis As Boolean

Const strXLPath As String = "C:\RFI\Excel Sheets\Organ transplants.xls"

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(strXLPath)

'loop through the shhets and delete as appropriate
For Each xlSht In xlWB.Worksheets
blnDeleteThis = True
For Each para In ThisDocument.Bookmarks("bmkTransplantTypes").Range.Paragraphs
If para.Range.Text = xlSht.Name Then
blnDeleteThis = False
Exit For
End If
Next
If blnDeleteThis Then xlSht.Delete
Next

Set xlApp = Nothing
Set xlWB = Nothing
'show the save as dialog
Application.Dialogs(xlDialogSaveAs).Show

End Sub[/VBA]I've assumed that each entry in the bookmarked text is it's own paragraph, maybe its comma delimited, in which case, you can Split the string into an array and loop through that.
Depending on how that text is formatted in Word, you may also have to trim off leading and trailing spaces and other non-printing characters to get a match with your worksheet name so a little testing will be required.
Let us know how you get on and let's hope the transplants continue without incident

[JohnnyBravo]

I think you should hire a professional consultant when your messing with peoples' lives. People are generous and your project is interesting. Maybe the people that hire you to computurize their records should reconsider their hiring/contracting practises before someone gets hurt and they become negligent. A "glitch" could cost someone their life. Their is alot of great free learning to be had here but if you are trying to pass yourself off as knowlegable perhaps that should stop before someone gets hurt? Dave

Before this thread erupts into a flame war - I'm going to tell you two things and I want you to read very carefully, because from this point I will not respond to your assinine comments.

#1) I find it strange that you would assume to know what I do for a living and even more assume what these letters are for. And yes, Killian is right, these are simple instructions letters from the director of our department on how to fill out the RFI. In case, you didn't know what that means - it's called an Request for Information. And no, neither I nor my boss are involved in performing any medical procedures. If you wish to add furhter comments to this thread, I suggest you make it related to VBA (or this project) rather than making assumptions and making yourself look like an ignorant fool.

#2) See #1 above.

Good day.

[JohnnyBravo]

Killian,

A couple of follow up questions.

1) I am having some trouble w/ the code you gave me. I get a VBA error saying "User defined type not defined" at the first line where it says:

Dim xlApp As Excel.Application

2) Looking at your example above, I cannot figure out how to list the various transplant types. Where can i put in the variables like Heart, Liver, etc. I'm extremely new to VBA and don't even know what or how to use arrays.

BTW, I'm using MS Office 2002.

===================================================

Here's a little excerpt from the letter:

October 27, 2005


Dear Ms. ?Last_Name?:



Our XXXXXXX is currently requesting transplant program information from institutions interested in participating, and those currently participating in the network. Enclosed are the RFI and/or RFI Addendum(s) for the following transplant programs: Adult Heart, Lung, Liver, Pancrease, autologous, allogeneic and Pediatric Heart, Liver.
......

That last part in bold underline is bookmarked in my document as "TransplantType".

[Killian]

A calm and measured response, indeed - nice

So to your questions...
To use the Excel object model from Word, in your Word VBA project, you should add a "Reference" to the Excel object model
In the Visual Basic Editor, Tools>References, select "Microsoft Excel 10.0 Object Library"
That should solve your error.

Now my example assumed you had the types listed and bookmarked already - or do you need an input method for that?

[JohnnyBravo]

A calm and measured response, indeed - nice

Now my example assumed you had the types listed and bookmarked already - or do you need an input method for that?

Thanks Killian.

Yes, the reference solved the first question. But now I'm getting a different error message "Run time error 5941. A requested member of the collection does not exist". The following line is highlighted.

For Each para In ThisDocument.Bookmarks("bmkTransplantTypes").Range.Paragraphs

===================================

I think you may have misunderstood the general format of the letter. In the above excerpt I posted above, the hospital gets the workbook w/ the specific sheets that relate to their specialty.

Let me provide some more info.

As I said, there are a total of some 14 different types. A hospital may specialize in only 1 or perhaps as many as 10 different types.

Let's say they specialize in Adult Heart & Liver as well as Pediatric Lung.

When you open up the excel sheet, these are all the sheets you would see.

Instructions
Adult Heart
Adult Lung
Adult Liver
Heart-Lung
SPK
Intestine
Pediatric Heart
Pediatric Lung
Pediatric Liver

So back to the cover letter. In the above excerpt, I have to manually type in the transplant types that THAT specific hospital specializes in and say Dear Mr. so and so... here are the RFI documents... please fill out excel sheets that relate to your specialty... X, Y, Z. In this case, X, Y & Z happens to be
"Adult Heart, Liver and Pediatric Lung".

I hope that helps.

[Killian]

OK , that clears it up. (I think)
To keep things simple (maybe I'm being lazy) it would help a great deal if you typed it in explicitly, with a comma between each one - so to use your example, you would type in:

Adult Heart, Adult Liver, Pediatric Lung

Then the code below should work nicely. What's happening is, the "Split" function I use here puts makes an array containing each entry (seperated by commas). Each worksheet name is then checked against this array (with any spaces trimmed off the ends). If the sheet isn't in the array, it's deleted.

So you can see the importance of having an exact match between your typed list and the sheet names.
If you abbreviate "Adult Heart, Adult Liver" to "Adult Heart, Liver" and want to use extra bits of the english language, like "and" and punctuation it means we'll have to come up with some code to sort that all out as well.[VBA]Sub SomeSubName()

'In VBE>Tools>References, add a reference to the
'Microsoft Excel Object Library

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim arrTypes() As String
Dim i As Long
Dim blnDeleteThis As Boolean

Const strXLPath As String = "C:\Documents and Settings\Killian\Desktop\Surgery.xls"
'Const strXLPath As String = "C:\RFI\Excel Sheets\Organ transplants.xls"

arrTypes() = Split(ThisDocument.Bookmarks("bmkTransplantTypes").Range.Text, ",")

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(strXLPath)

'loop through the shhets and delete as appropriate
For Each xlSht In xlWB.Worksheets
blnDeleteThis = True
For i = LBound(arrTypes()) To UBound(arrTypes())
If Trim(arrTypes(i)) = xlSht.Name Then
blnDeleteThis = False
Exit For
End If
Next
If blnDeleteThis Then xlSht.Delete
Next

xlApp.Visible = True
xlApp.Dialogs(xlDialogSaveAs).Show

Set xlApp = Nothing
Set xlWB = Nothing

End Sub[/VBA]

[JohnnyBravo]

OK, perhaps I'm missing somethign here but I get a runtime error at the highlighted line:

arrTypes() = Split(ThisDocument.Bookmarks("bmkTransplantTypes").Range.Text, ",")

-----------------------------

I modified the path of the excel file to where it's located on our network drive.... and modified the bookmark name because in my letter it's named as "TransplantType" .... are there any other changes I need to make for this code to work?

EDIT: And i know what you're gonna ask next.. JB, have you separated the transplant types with commas? And the answer is Yes.

[Killian]

Hmm... well is the macro being run from the document that contains the bookmark?
If not, referring to "ThisDocument" is no good.
You can use "ActiveDocument" if your running the code when the document that contains the bookmark is the active window in Word.
Otherwise, we'll have to work out which doc to run the code on from the ones you have open

[JohnnyBravo]

Hmm... well is the macro being run from the document that contains the bookmark?
If not, referring to "ThisDocument" is no good.
You can use "ActiveDocument" if your running the code when the document that contains the bookmark is the active window in Word.
Otherwise, we'll have to work out which doc to run the code on from the ones you have open

OK - now the code runs - sort of....

The desire excel file comes up, but the sheets are not deleted. All i get is a File 'Save As' prompt. I did so, but the sheets are not being deleted.

[Killian]

Well we're getting there.......
I'm afraid I have to go right now, but here's the files I tested it on - seems to work ok (you'll have to change the path as you know)
I'll try to check back later - if not I'll be back tomorrow

In the meantime, Select View>Watch Window in the VBE, then in the loop code, select
Trim(arrTypes(i)), right-click and add a watch.
Do the same for xlSht.Name
Now run the code line by line by pressing F8 (or add some breakpoints) and check the values that are being compared to see where the problem is.

[JohnnyBravo]

Killian,

First of all, I realized that the excel sheets in question had a password protection. I didn't think that would matter because upto now, I have been manually deleting specific sheets in question without having to "unprotect" them. But just as a safe measure I unprotected them all - and the code still won't work.

Then I thought there must be something about hte security settings on the company's network that prevents vba from running so i copied the excel file off the network onto my local hard drive and it still won't work.

The code works perfectly with the documents provided in your zip file but does not work on the files I have (even w/ the above changes). So there must be something about my excel file which is preventing the code from running - I can't figure it out. I'll add the watch in the window and see what happens next..... i'll report back.