Microsoft Office Forums Word mail merge from a protected Excel worksheet

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-23-2011, 04:33 AM
Platform Platform is offline Word mail merge from a protected Excel worksheet Windows Vista Word mail merge from a protected Excel worksheet Office 2007
Novice
Word mail merge from a protected Excel worksheet
 
Join Date: Sep 2011
Posts: 7
Platform is on a distinguished road
Default Word mail merge from a protected Excel worksheet

Hi guys



As the title says, i'm looking to merge from an Excel workbook that is protected (well, the majority of it is anyway).

Is there any way around this? I know the sheet in question could probably be copied into a new book and then merged but this could prove a bit long-winded for my client who i'm trying to explain this all to.

I'd like to add, i'm no expert either so please treat me with kid gloves when replying!

Thanks in advance,

Kev
Reply With Quote
  #2  
Old 09-26-2011, 03:41 AM
OTPM OTPM is offline Word mail merge from a protected Excel worksheet Windows 7 32bit Word mail merge from a protected Excel worksheet Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi Kev
The fact that your Excel worksheet is protected should not make any difference. I have tested a protected worksheet mailmerge on my machine and it works fine.
Can you advise, step by step, where your process is failing so we can help you further.
Tony(OTPM)
Reply With Quote
  #3  
Old 09-26-2011, 05:43 AM
Platform Platform is offline Word mail merge from a protected Excel worksheet Windows Vista Word mail merge from a protected Excel worksheet Office 2007
Novice
Word mail merge from a protected Excel worksheet
 
Join Date: Sep 2011
Posts: 7
Platform is on a distinguished road
Default

You know, I think you're right as i've just tried the same. I've created my excel doc (50+ sheets) and also created a word template with the required fields which was working fine last week. Now though, when I go into word>mailings>select recip>use existing list it bring up message 'error has occured: external table is not in the expected format.'

Any immediate ideas?
Reply With Quote
  #4  
Old 09-26-2011, 07:45 AM
OTPM OTPM is offline Word mail merge from a protected Excel worksheet Windows 7 32bit Word mail merge from a protected Excel worksheet Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
Make sure every column in your recipient list has a header, if not it will fail.
Good luck.
Tony
Reply With Quote
  #5  
Old 09-27-2011, 01:56 AM
Platform Platform is offline Word mail merge from a protected Excel worksheet Windows Vista Word mail merge from a protected Excel worksheet Office 2007
Novice
Word mail merge from a protected Excel worksheet
 
Join Date: Sep 2011
Posts: 7
Platform is on a distinguished road
Default

All headers are present. I just cant understand it, it was working fine last week. I have made some additions since then admittedly but nothing that should cause this to happen. It took me daaaaays to create, can't face having to do it again!!
Reply With Quote
  #6  
Old 09-27-2011, 02:54 AM
Platform Platform is offline Word mail merge from a protected Excel worksheet Windows Vista Word mail merge from a protected Excel worksheet Office 2007
Novice
Word mail merge from a protected Excel worksheet
 
Join Date: Sep 2011
Posts: 7
Platform is on a distinguished road
Default

I've now just found out the merge will work IF the excel file is already open so I would presume it's a password issue?
Reply With Quote
  #7  
Old 09-27-2011, 03:19 AM
Platform Platform is offline Word mail merge from a protected Excel worksheet Windows Vista Word mail merge from a protected Excel worksheet Office 2007
Novice
Word mail merge from a protected Excel worksheet
 
Join Date: Sep 2011
Posts: 7
Platform is on a distinguished road
Default

Ok so i've now gone into my word template and done the following:

Word Office button>Word Options>Advanced>General and checked the "Confirm file format conversion on open" box. Clicked ok, re-saved the doc and exited.

I then re-open said template and go to mailings>select recips>use existing list and select the relevant workbook. It then brings up the Confirm Data Source box. I check the "show all" field at the bottom and hit ok. I'm then lead to believe I should be using the "MS Excel Worksheet via DDE" option? However, when I do I don't seem to be able to extract the data from the specific worksheet I require.

Getting there slowly but could perhaps do with a hand on this last but if anyone's got a suggestion?
Reply With Quote
  #8  
Old 09-27-2011, 04:21 AM
OTPM OTPM is offline Word mail merge from a protected Excel worksheet Windows 7 32bit Word mail merge from a protected Excel worksheet Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
I am using Office 2010. However I do not get the option of using MS Excel DDE. Are you able to bypass that option. I have tested it in 2010 and there is no option to use DDE and it works fine for me.
If this is still an issue can you post a screenshot that shows the DDE option for me to look at.
Good luck.
Tony(OTPM)
Reply With Quote
  #9  
Old 09-27-2011, 11:43 AM
Platform Platform is offline Word mail merge from a protected Excel worksheet Windows Vista Word mail merge from a protected Excel worksheet Office 2007
Novice
Word mail merge from a protected Excel worksheet
 
Join Date: Sep 2011
Posts: 7
Platform is on a distinguished road
Default

Hopefully i've attached it to this msg....
Attached Images
File Type: jpg MS.jpg (46.2 KB, 11 views)
Reply With Quote
  #10  
Old 09-28-2011, 01:22 AM
OTPM OTPM is offline Word mail merge from a protected Excel worksheet Windows 7 32bit Word mail merge from a protected Excel worksheet Office 2010 32bit
Expert
 
Join Date: Apr 2011
Location: West Midlands
Posts: 981
OTPM is on a distinguished road
Default

Hi
Follow these steps in order. I use Office 2010 but these steps are from Office 2007:
1. Open MS Word.
2. Click on the Mailings Tab.
3. Click on Select Recipients Button.
4. Click on Use Existing List.
5. Open the folder containing your client data and click on the file name.
6. Click on Open.
7. Click on the Sheet Name that contains your client data.
8. Click OK.
9. Click on Insert Merge Field and select each merge field you wish to insert into your mailmerge document.
10. Click on Start Mail Merge button.
11. Select Step by Step Mail Merge Wizard.
12. Click the Letters Radio button.
13. Click on the "Next: Starting Document" hyperlink.
14. Click the Use the current document" radio button.
15. Click on the "Next: Select recipients" hyperlink.
16. Click on the "Use existing list" hyperlink.
17. Click on the "Browse" hyperlink button and navigate to your client data spreadsheet and click on the filename.
18. Select the worksheet containing your client data.
19. Click OK.
20. You will then be presented with the list of records. You will now be able to de-select those records you dont wish to Merge if required.
21. Click OK.
22. Click on the "Next: Write your letter" hyperlink.
23. Click on "Next: Review your letter" hyperlink.
24. The first record will be merged.
25. To complete the mailmerge process simply click on the "Next: Complete the merge" hyperlink.
These are all the steps you should need to use. You should not have to select any DDE options.
Good luck and let me know how you get on.
Tony(OTPM)
Reply With Quote
  #11  
Old 09-28-2011, 05:12 AM
Platform Platform is offline Word mail merge from a protected Excel worksheet Windows Vista Word mail merge from a protected Excel worksheet Office 2007
Novice
Word mail merge from a protected Excel worksheet
 
Join Date: Sep 2011
Posts: 7
Platform is on a distinguished road
Default

Hi Tony

Forgive me for sounding rude but i'm quite aware how to do a basic mailmerge. The problem i'm having as per my original post is that when I reach point 6 on your list and click open it brings up the error msg 'error has occured: external table is not in the expected format.' However, if the source spreadsheet is already open it does not bring up said error and allows me to proceed as normal. Keeping it open is an interim solution but does not get to the root of the problem.

After a bit of googling I have proceeded to post number 7 of this thread which is where I am still at now...
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Import field from Excel to Powerpoint xgravity23 Office 8 11-22-2012 02:08 PM
Excel 2007 to Word 2007 Labels mail merge not working rec Mail Merge 0 07-31-2010 10:45 AM
Word mail merge from a protected Excel worksheet VB Code in Excel Active worksheet shakilhyd Excel 2 05-17-2010 07:50 AM
Help with switches: Word mail merge with Excel ks_ Mail Merge 0 12-09-2009 05:09 PM
Word mail merge from a protected Excel worksheet mail receipent with an excel worksheet attachment jburke@holidayinnislip.co Office 1 11-14-2005 08:55 AM


All times are GMT -7. The time now is 04:10 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft