Mail Merge - Word / Excel

[Rundvelt]

Ok, this is a fairly drawn out explanation, so please bear with me.

A little while ago, the secretaries in our department figured out how to use mail merge. However, in true user fashion they use it poorly.

They have these excel spreadsheets that they use to book patients, however, they use all sorts of funky colors, formats and stuff. This happens to break the mail merge linkage (Through Excel corruption I believe).

ANYWAYS, we're migrating to a Filemaker database by September and I have been charged with patching the connection until it's implemented.

To fix this I took the information from the page, determined if it's a Number or Text, and then transferred it to another page which mail merge will read from. So, the sheet list looks like this... Data / Secretary / Calculation (Where the cells in secretary are determined if they are number or text based).

However, the secretaries need to be able to insert rows which happens to break my nicely fit equations. I tried putting in absolute values "$" however, excel still modifies them when a row is inserted or deleted.

Is there any way to stop this? Will creating a second excel sheet to import the data stop the formulas from being tampered with?

If I can keep the equations in the same spot without excel moving or modifying them, the sheet will work.

Thanks for your time and suggestions.

 

[SkipVought]

Hi,

What equations are you using?

What do they reference?

I see no reason why FORMATING in Excel would "break" the database used for Word mailmerge. However, inserting blank rows might throw a monkey wrench in the works.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Rundvelt]

Ok, I have three sheets. The first is where I store the raw data, let's call it Data. The second sheet is the sheet where the secretaries work. Let's call that information. The last page determines what is in the cells, be it a number or text, so let's call this calculations.

Here's how it works.

Let's say for cell A1.

=ISNUMBER(Information!$A$1)
=IF(Calculations!$A$1=TRUE,N(Information!$A$1),T(Information!$A$1))

This basically takes the formatting out of the information page so the DDE link between Word and Excel is valid.

Formatting does break the program link, as well as comments, strikethrough or pretty much any change below a certain line. I suppose the source of this corruption might be from an inserted line, however, we have other secretaries who do this all the time and there is no problems with their mail merge.

Not saying you're wrong, just saying that mail merge is "iffy" at best and is a genuinely unreliable solution for
mass notes. (An actual database being a better solution.)

 

[SkipVought]

So all of the sudden you're talking about a DDE link? Where did that come from? The initial question involved MailMerge in Word from Excel.

They have these excel spreadsheets that they use to book patients, however, they use all sorts of funky colors, formats and stuff. This happens to break the mail merge linkage...

...Not saying you're wrong, just saying that mail merge is "iffy" at best and is a genuinely unreliable solution for mass notes.

So, let's start all over.

Exactly what is the problem?

What are you trying to accomplish?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Rundvelt]

Sorry Skip, perhaps I wasn't as articulate as I should have been, was a bit cramped for time.

We have secretaries. They use Excel to enter data about appointments. These databases somehow break the mail merge link every so often, be it a DDE error or word crashing or it just not showing up (it's a whole miriad of problems, not just one consistant error). These errors, because they are so varied, take up all of our time to diagnose. We end up rolling back to a previous saved version which restores the link (but removes the secretary's work). They then resume the work and break the file.

I am trying to fix this and stablize the mail merge link so that the secretaries can still use all of the tools they need, yet remove the inherant problems their spreadsheets are having.

As mail merge only "looks" at the first page of excel, I was attempting to take the cell information (that contains color, different fonts, different formatting, etc) and extract the numbers or text only.

I did this by having a calculation sheet and a data sheet. That way excel could determine if the cell contained numbers or text. Then the other page, based on the true/false result of ISNUMBER could use =T (for text) or =N (for numbers)

When I did this, the DDE error disappeared and functionality was restored. However, when people insert new rows, the formulas break and are modified automatically by Excel. I have tried to make the equations absolute values (Ie. $A$1) however, the inserted row still modifies them. Is there any way to tell excel not to automatically update the formula?

Thanks.

(PS, if there isn't a way to stop this, any other additional suggestions would be very helpful. We are considering scripting or a visual basic macro should this formula process fail.)

Perhaps that explains the situation and my goals a bit better. Sorry for the confusion.

 

[SkipVought]

I would not use formulas; rather VBA code using the same kind of logic. The code must fire on some event, like the workcheet_change event.

If you choose to go this route, take this question to the VBA Forum707.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[DPlank]

Instead of referencing the fields (either relatively or absolutely), can you not simply name the cells you want to use? The cell name will follow it if you add/remove rows.

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!

 

[SkipVought]

This may help with Dave's suggestion

How can I rename a table as it changes size faq68-1331

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[DPlank]

Thanks for the assist Skip - I always forget to note the FAQs in existence...

Cheers,
Dave

Probably the only Test Analyst Manager on Tek-Tips...therefore whatever it was that went wrong, I'm to blame...

animadverto vos in Abyssus!

Take a look at Forum1393!