Old versions of worksheet visible when I link to excel from word mail merge

[Bob Sawyer]

Hopefully this is a very simple problem and I've just missed something obvious. I'm using Office 2013:

1. I create an excel file (exported in excel format from Access table)

2. I open the data in excel and do a few bits and bobs - delete a column or 2, add a calculated field, nothing too complicated

3. When I do a mail merge in Word and link to the excel file, instead of there just being the one sheet to choose from as a data source, I get 2. My sheet was called 'OA28962UKVIP_OUT', but I get a choice of 2 different data sets. One of these 'tables' is the sheet as I want it, the other one seems to be a copy of how the data was before I made any changes to it - like a backup.

It looks like this in Word's mail merge helper screen:

When I send the sheet to one of my colleagues to mail-merge they inevitably pick the wrong option at the merge stage and then cannot see the fields I changed/created.

My excel file just displays one sheet like this:

Can anyone please tell me what is going on here and how to prevent it? - it's driving me mad and I can't tell what the cause is! - all I want is for the word merge to show just one option for data.

Thanks in advance!!

 

[Macropod]

If you open Excel's Name Manager (Formulas|Name Manager), you may find there's a range named OA28962UKVIP_OUT. Try deleting the name.

 

[Bob Sawyer]

Hi Macropod - thanks for the reply.

You nailed it, it was in Name Manager. When I delete it removed the 'extra' data source.

Could you help me understand why this gets created though?

I can't seem to tell what prompts Excel to do it - I tried adding columns to a sheet and putting in some formula, but I can't see when it happens.

Is there a way to disable the auto-creation of named ranges so I don't have to keep checking if they are there before I close a sheet, and deleting if they are? - they are definitely useful if under my control, but in this case they are definitely unwanted!

Thanks!

 

[Macropod]

I have no idea why or how it got created; I just know that I've seen the same phenomenon in other workbooks that have been attached to posts in other forums.

 

[Bob Sawyer]

I have no idea why or how it got created; I just know that I've seen the same phenomenon in other workbooks that have been attached to posts in other forums.

OK thanks. I'll just have to remember to delete manually every time - which is going to get old pretty fast.

 

[Macropod]

There's no need to do it manually - you could use a macro like:

Sub DelNames()
While ActiveWorkbook.Names.Count > 0
  ActiveWorkbook.Names(1).Delete
Wend
End Sub

 

[Bob Sawyer]

Hi Macropod

Thanks - useful, but I'd still have to remember to run the macro. Unless of course it's possible to trigger a macro when you save ANY sheet?

I know you can add one to saving a specific sheet as you can use the event, but is there a way to do it across the board?

 

[Macropod]

You probably wouldn't want a macro to remove the names from every workbook you open; after all, named ranges are very useful in some contexts. If you're using code to export to Excel from Access, that same code could probably be modified to delete the name(s) from the workbook when it is created.

 

[Bob Sawyer]

You probably wouldn't want a macro to remove the names from every workbook you open; after all, named ranges are very useful in some contexts. If you're using code to export to Excel from Access, that same code could probably be modified to delete the name(s) from the workbook when it is created.

True, named ranges can be useful indeed - I just want more control over their creation.

Controlling from Access would help, but I usually do a couple of bits in Excel after taking the data from Access to make use of some of a few macros that just work better in an excel environment. I could run these also from Access as part of the export, but it's starting to look like the solution would be more effort than it's worth. I'll just put the 'delete named range' macro on a button on my toolbar and use that.

Thanks for your help with this - you really know your stuff!

 

[Macropod]

Controlling from Access would help, but I usually do a couple of bits in Excel after taking the data from Access to make use of some of a few macros that just work better in an excel environment.

Any of those Excel macros could likewise be modified...