Word / Excel Mail Merge VBA Assistance... 1

[Rundvelt]

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?



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,



I'm not a programmer, but from what I've been hearing it might be an easy fix.

 

[TonyJollans]

Hi Skip,

Yes, there are occasions when Excel's automatic adjustment is not what you want. The way to avoid it is to use INDIRECT - it's a compromise, not a perfect solution but it does give completely absolute addresses - instead of "$A$1", use INDIRECT("A1")

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Rundvelt]

Sorry, how would I use that in an equation. For example if I needed the equation =ISNUMBER(Sheet1!$A$1)

Thanks.

 

[TonyJollans]

Hi Skip,

[blue][tt]=ISNUMBER([/tt][/blue][red][tt]INDIRECT([/tt][/red][blue][tt]Sheet1!$A$1[/tt][/blue][red][tt])[/tt][/red][blue][tt])[/tt][/blue]

but you don't actually need the [blue][tt]$[/tt][/blue] as the address is resolved when the formula is evaluated and the reference should always be absolute.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Rundvelt]

Actually, that didn't work. I inserted a column on the other sheet and it modified the formulas.

Inserted a line below 13. Making a new 14 and moving one down to 15. On the equation sheet, line 13 remains 13, but line 14 now has the =isnumber(indirect(sheet1!$A$15))

Excel still seems to be moving the numbers around.

 

[TonyJollans]

Oops, sorry! You need quotes around the address ..

[blue][tt]=ISNUMBER([/tt][/blue][red][tt]INDIRECT([/tt][/red][COLOR=red yellow][tt]"[/tt][/color][blue][tt]Sheet1!$A$1[/tt][/blue][COLOR=red yellow][tt]"[/tt][/color][red][tt])[/tt][/red][blue][tt])[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Rundvelt]

One last question if you'd be so kind.

I have about 60,000 entries I need to modify. How can I do it quickly (Can't cut and copy)

 

[TonyJollans]

That's quite a lot. Are they in a single worksheet or workbook? How are you going to identify them?

A couple of possibilities might be a VBA routine that looped through cells - or saving as CSV (or some sort of text - maybe xml - depends on your version) and then doing a find and replace (possibly using Word so you can use a wildcard search) - I'd need more information to be sure of the best way.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Rundvelt]

I was trying to use excel's find and replace wildcards.

For example...

Find: =ISNUMBER("INDIRECT(INFORMATION!$A$*"))
Replace: =ISNUMBER("INDIRECT(INFORMATION!$A$*"))

That's just an example of course, it doesn't actually work.

I'm fairly new to the whole "IT" world, so any help you'd be willing to provide would be great.

I need to have the VBA cycle through 2000 rows, spanning from column letters a through r. I thought I'd have to do it for another sheet, but that appears not to be neccessary.

So, 2000 lines per letter, a -> r, is about 36,000 lines that need to be changed. A weekend of work.

Obviously I'd like to try and bypass that.

 

[Rundvelt]

Actually, nevermind, took a low tech approach...

Find: =ISN
Replace: ISN

Find: (INFOR
Replace: ("INFOR

Find: ))
Replace: "))

Find: ISN
Replace: =ISN

Low tech but it works really quick. Sorry for the post without thinking it through