Forum Discussion
Mail merge
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?).
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.
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.
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.
