delete page in Word, based on Excel row delete

[kawnz]

Our medical billing system has the ability to generate letters asking our clients to supply information that was missing on the original order. We need this information in order to bill insurance.

The system generates two things: an edit report that our employees can use to fix any errors that might have been mis-keyed, and the other is the missing information letter, where we ask the client for the information.

The missing information letter is based on the edit report - i.e. we can set which edit report errors to print on the letters.

However, the missing information letters are limited to either include or exclude criteria, not both. E.g. we might want to include accounts that have certain errors, but exclude accounts that have other errors.

The result is that too many letters are generated by the computer, leaving us to manually go through the piles of letters and pull out the letters we don't want to print. At last check, the total number of letters the system generated was 24000 - and we only needed to send 10% of them.

We have remedied the paper waste somewhat by first printing the letters to a file, opening the file in Word, and deleting each page we don't need. It's quicker and wastes fewer trees.

My colleague is happy enough to use the Word method to get rid of letters she doesn't need before she prints them, but is now asked to keep a spreadsheet of all the letters sent, deleted, etc.

When I found that out, I told her I would give her a spreadsheet of all the accounts that are in her missing information file, so she can mark them deleted in her spreadsheet, rather than type the account numbers and such in excel.

Now I am thinking/hoping that there is a way to link the spreadsheet and the letter file, and basically, if the spreadsheet has the word "DELETE" in a column, that Word can look through the file and delete the page that corresponds with that account number.

If nothing else, this method would allow us to eliminate accounts where the computer has a record that we did the work, but we haven't received the paper copy of the order. We can generate an edit report/spreadsheet that shows these accounts.

The spreadsheet has the account number in Column A. I need for Word to find each account number, and then delete the page that displays that account number. I have never done cross-application macros before, so am not sure where to begin.

I would appreciate any help you can provide.

 

[kawnz]

let me clarify...
essentially what I want, is for word to do a find of each account number in the excel file, and then delete the page if it is there.

I figure that I can have the lady do filters with delete/send and then have the word doc look through the spreadsheet to select the pages to delete.

 

[fumei]

essentially what I want, is for word to do a find of each account number in the excel file, and then delete the page if it is there. {/quote]

I know that was a great effort in trying to be clear, but I am still a bit confused.

Word searches Excel for an account number.
Word deletes an EXISTING page in some document if the account number is in the Excel file?

OK, could you describe the generating syetm a bit more? It generates letters based on SOME criteria, but apparently it is not fussy about it.

So you have a Word document, an existing Word document. I am not clear if this is ONE document that is generated. Your post is written as if it is. ONE big document containing various letter parts. Someone seems to be pulling out parts that are not needed. Would not your system generate separate letters for separate clients?

OK, maybe it does, and the mushed document (with some parts you want mailed, some parts you do not) is for one client. I hope so. How are the parts separated? Are they clearly delineated by separate pages? How are you thinking of identifying the page?

Say you read back to the Excel file, somehow there is a field that says "Delete".

spreadsheet has the word "DELETE" in a column, that Word can look through the file and delete the page that corresponds with that account number

Absolutely Word could do that...if there is some identification of page to account number. Are they separated by Sections? Are they always one page ONLY? Is the account number contain in a Bookmark? In a Field?

As a general concept...yup, I think this can be done. A few more precise details will move it along.

Gerry

 

[kawnz]

Word searches Excel for an account number.
Word deletes an EXISTING page in some document if the account number is in the Excel file?

Yes, that is essentially what I am looking for.

OK, could you describe the generating syetm a bit more? It generates letters based on SOME criteria, but apparently it is not fussy about it.

The edit report is more precise and is able to 'weed out' based on more criteria, like users, etc. The letters only have one criteria: errors

So, if there are other criteria that we want to exclude from the letters, we have to do it manually. My aim is to generate the edit report to a spreadsheet (thanks again fredericonseca for your help with that), based on the criteria that we want to delete.

So you have a Word document, an existing Word document. I am not clear if this is ONE document that is generated. Your post is written as if it is. ONE big document containing various letter parts. Someone seems to be pulling out parts that are not needed. Would not your system generate separate letters for separate clients?

OK, maybe it does, and the mushed document (with some parts you want mailed, some parts you do not) is for one client. I hope so. How are the parts separated? Are they clearly delineated by separate pages? How are you thinking of identifying the page?

The program generates one big text file. Each client is within this large file. The first page for every client is a letter telling them that we need missing information. It is then followed by each account requiring this missing information. Each account is on its own page.

When I open this text file in Word, there are page breaks between the letter and the first account and also between each account.

We are able to print only certain clients, but since we have over 500 clients, this really is not an option.

Say you read back to the Excel file, somehow there is a field that says "Delete".

Quote:
spreadsheet has the word "DELETE" in a column, that Word can look through the file and delete the page that corresponds with that account number

This part I am still thinking about. There will be a column in the spreadsheet that says delete. That is so our people can track which letters are sent and which are deleted.

However, I'd also like the capability of running a report based on the criteria we want to exclude, and then basing the deletion on that list.

Absolutely Word could do that...if there is some identification of page to account number. Are they separated by Sections? Are they always one page ONLY? Is the account number contain in a Bookmark? In a Field?

Each account is separated by a page break. The account number is not contained in a bookmark or a field. It is just a straight text file with no special features.

I am thinking we basically have to automate Word's find feature and just look for the accounts that way?

I hope this helps.

 

[fumei]

Yes it does. i have a huge morning today, but will come back to this this afternoon.

Essentially though, I think it would be better to do it in stages.

Build a routine that identifies chunks of the text file by account.

is it possible the same account could have relevant text scattered in different places? Or would all information relevant to ONE account be contiguous?

If the latter, it would make it much easier.

If the the accounts are separated by hard page breaks, followed by account number, I would first run through the document searching for:

page break followed by account number.

it would be easy to loop through the account numbers. Well, it should not be too difficult. Replacing the page breaks by section breaks, thus compartmentalizing each account into its own section. This makes it easier to deal with accounts as entities, and the relevant text for each as a manageable chunk.

Gerry

 

[kawnz]

This is an example of what the letter looks like.


COMPANY NAME
COMPANY ADDRESS
COMPANY ADDRESS

01/06/2005

CLIENT NAME
CLIENT ADDRESS LINE
CLIENT ADDRESS LINE


Body of the letter.

Please provide us with the missing patient information to
expedite the claim process for the patients.

More body

Return Address Information


{page break}

COMPANY NAME
COMPANY ADDRESS
COMPANY ADDRESS

Missing/Incorrect Information for Client 4010 - CLIENT NAME

Accession Patient Name/Payor Service Tests Ordered
____________________ ______________________________ __________ _________________
143L11111 PATIENT NAME 11/15/2004 C300490 (TEST)
BSVA - BLUE CROSS-BLUE SHIELD C300650(TEST)
P - PATIENT P800325(TEST)
P800695(TEST)
P801110(TEST)

Missing or Incorrect Information:

DIAGNOSIS IS REQUIRED FOR ALL COMPONENTS OR THE D.O.S. - BSVA
--------------------------------------------------------------------------------



Thank you for your cooperation. ------------------------- ----------
Physician signature Date


{page break}
New page with next account.

The page breaks in the text file look like that little box character []

 

[fumei]

Sorry, I am running to the airport and flying out in 30 minutes, but I will try to answer more.

I am still a little unclear as to if this is one large document, or separate ones. I think it is one large one.

So. It appears that there are TWO pages per account. Would it always be that way?

It appears that the separation is by Company name, NOT account number. Is this correct? Is there an account number to use for this?

Here are some thoughts....Make some variables:

Dim strCompany As String
Dim r As Range

Do a search for strCompany, and if found, place a temporary bookmark (named "temp1"), search for a Chr(12) - a page break - and then search for another one. This gets two pages. Backup one character (just before the 2nd page break), and then set the 2nd temporary bookmark.

Ok, now you have the section of text for that company - and there may still be a problem, as I don't know whether there will be multiple instances of the same company. I imagine there may be, and that will have to be dealt with.

However, for the time being, now make a range of the text between the temporary bookmarks. I would make the Delete data from Excel boolean, so if it is True, then you delete the created range. Those pages will be deleted.

Delete the temporary bookmarks, and go on to the next company.


Gerry

 

[kawnz]

Sorry, I guess I am not being very clear.

There is one large document that includes all letters. As stated in my first post, there could be 10000+ pages worth of accounts in the file generated by the billing system.

The Company name is our company name, and is listed at the top of every page, so would not do well to search based on that.

Each client gets a letter preceeding the pages that have the account number for that client. The letter to the client lists the client's name, and address. It does not refer to a client number at all on the "letter" page.

Each account/accession page has the client number and name as well as the patient's account number. It is the patient's account number that I am checking on.

I played with some code today, and the following seems to do what it needs to as far as the functionality goes.

Sub DeleteAccountsinWord()

'   Information from worksheet
    Set Data = Sheets("Sheet1").Range("A1")
    
'   Count how many rows there are.
    Records = Application.CountA(Sheets("Sheet1").Range("A:A"))
    For i = 1 To Records
    
'   Assign current data to variables
    Account = Data.Cells(i, 1).Value
    PtName = Data.Cells(i, 2).Value
    Client = Data.Cells(i, 3).Value
    Dollar_Value = Data.Cells(i, 4).Value
    
     
'   Send commands to Word

Word.Selection.Find.ClearFormatting
       
   With Word.Selection.Find
        .Text = Account
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With  ' Word.Selection.Find
    If Word.Selection.Find.Execute = True Then
        ActiveDocument.Bookmarks("\Page").Range.Delete
    End If 'Selection is True
    
    
    Next i
End Sub

Of course, running this macro from Excel would have it delete the accounts from any Word doc, so more functionality needs to be added to make this user-friendly (idiot-proof) - but is this a good starting point?