Mail merge

[ecovonrein]

Does anyone here have experience with merging Excel data into Word documents?  I had one of those days with Office, I would sooner forget.  I have an XLM file - not too heavy, but with OnCalculate VBA - which used to supply data into Word without any problems when we were still using Dropbox and pre-365 environment.  Since moving to O365 and SPO, the Word will happily take 15 minutes over opening the document. 

There is some weird trick by which I can force Word to give up on DDE and switch to something called OLEDB.  It brings the time down to 1 mins - still pretty rubbish.  I talked to my new friend ChatGBT about it and I now know that I am in trouble because, after initially coming up with many helpful suggestions, it in the end referred me to Microsoft Support ... 😞

One of its suggestions was to rebuild the Word doc from scratch.  That wasn't a big deal because the doc in question is a one-pager.  A bit of CTRL-C/CTRL-V got the job done in no time.  It also suggested that I move the Excel off SPO.  I saved a copy into Downloads before making a new connection from the new doc.  But here is the weirdest thing:  when the OLEDB connections eventually resolved - we are talking here no less than 5 mins - the fields in Word did not contain the values from the open Excel.  The values that displayed in the Recipient list were values from the file on SPO.  Not that Word would actually populate the preview mailings.

Does this ring any bells with anyone?  Thanks.

[#2]

@ecovonrein 

Does anyone here have experience with merging Excel data into Word documents?

Yes. And I'm quite confident I'm not alone in that.

That having been said, I notice your question has had over 175 views with no replies. I don't know that this is the case, but I suspect the lack of replies might be due to the fact that  most of the rest of your post is spent complaining about things without really giving anything to address constructively. And you're fond of acronyms that are at best ambiguous--notably SPO; try Googling it to see what I mean.

So back to your original question; I do have some experience with merging data from Excel into Word documents, a good deal of complex merges in fact--though the really complex stuff was well over 20 years ago--but I'm wondering whether you're making things more complicated than they need to be, or asking the merge process to do things it just isn't designed to do. From all that you've written, it's clear that you're not a novice with Excel and its capabilities, so any experience that I (or others) have may be redundant with what you already know and have tried. I just can't help but wonder if you could use all that expertise to get to a simple flat file in Excel before making the connection to Merge (or is that what you're saying that you've done?).

[#3]

@mathetesThe short answer is "No".  A simple flat file does nothing for me when our production environment is as heavy as it is.  SPO stands for SharePoint Online.

If you have to wait 15 mins for something to happen, you'd probably complain too.  I was about to throw the laptop at the wall.  My personal highlight of that day was ChatGBT helpfully suggesting that I might be better off coding the whole merge in VBA.  Don't say that little bot does not have a sense of humour.

Anyway, the largest part to the answer I meantime discovered.  It turns out that Word *always* latches on to the the most recent Excel session, and NOT the session with the target database. So ... when that most recent Excel session does not contain that database, Word will launch it again.  Which is disaster for two reasons:

1  You won't get the data in the active session, obviously.

2  If the most recent session is heavy - and that it was in my case - the act of loading the read-only copy from disk and calculating it ends in a fiasco.  Now, 15 mins easily exceeds the time it should take to calculate even the heaviest of my Excels, but Lord knows what other nonsense that process does. I would not be surprised if it ran single-threaded.  Just for fun.

Anyway, the bottom line is that I can get the link-up time down to 2 mins if I make sure that I open the database in a new Excel session.  Those 2 mins are still worse than what it was 5 years ago, but hey - that is the price of progress I suppose.

And I could rant about SPO all day long 😉

PS:  I also observe that the obscure OLEDB *always* opens a read-only copy of the Excel from disk, so it will never get the latest values from a recalculated template.  So the default DDE is the only way for us to go.

[#4]

@ecovonrein 

Thanks for such a thorough reply. As I noted before, you are clearly very knowledgeable with things Excel. So I wish you well in this....perhaps my having answered, and you having replied, will bring the whole issue to the top of the forum for some other looks by folks with more recent and comparable experience. 

[#5]

@mathetesHa ha.  I doubt it.  I nodded along when you wrote "the really complex stuff was well over 20 years ago".  I think mail merge has gone out of fashion.  I have not come across anyone using it in this millennium.  It is what oldtimers like us still occasionally do 🙂  Probably also the reason it no longer works.  Not a focus for MS.

Best.

[#6]

@ecovonrein 

I'm retired now, in my early 80s, still enjoying figuring out how to do things in Excel, and quite overwhelmed by all the new functions and features. I particularly enjoy the dynamic array functions.

You may be right about Mail Merge. What I recall doing with it that I considered complex was writing a routine that used mail merge to create letters of agreement for employees of a division being sold to another company, and Word had to list variable numbers of benefits that could be (or couldn't be) transferred to the acquiring entity. This would have been in the 1990s...

[#7]

Thanks all for answering.