names not working after opening programmatically

[VerlindenG]

Excel2010 - OfficeXP and Office2010
I have a pretty big workbook with much names defined and used in it.
When I do open this workbook manually, no problem.
But when I open the workbook in VBA (Workbooks.Open (Filename) not one of the names seems to be recognised: every formala referencing a name gives the "#NAME?" error.

- Recalculating doesn't help.
- When I trie creating a new workbook with names, I do not have the same problem, but this is not really an option for the moment being because my actual workbook with the problem is huge.

Has anyone an idea how to get rid of this problem?

 

[JLGWhiz]

Excel2010 - OfficeXP and Office2010
I have a pretty big workbook with much names defined and used in it.
When I do open this workbook manually, no problem.
But when I open the workbook in VBA (Workbooks.Open (Filename) not one of the names seems to be recognised: every formala referencing a name gives the "#NAME?" error.

- Recalculating doesn't help.
- When I trie creating a new workbook with names, I do not have the same problem, but this is not really an option for the moment being because my actual workbook with the problem is huge.

Has anyone an idea how to get rid of this problem?

Not sure that this will help, but it won't hurt to look at it:

http://exceluser.com/blog/4/excel-bug-deletes-some-external-range-names-automatically.html

You will need to read down a couple of paragraphs before it gets to your problem specifically.

 

[VerlindenG]

Well, indeed, it didn't hurt to look at it. I've got the impression that it has something to do with it, but I was not able to resolve it.
Anyway:
1) I don't use "sheet-scoped" names, only "workbook-scoped" names.
2) I must admit that I do not really understand what is meant by "report-" and "database"- workbooks. An add-in, is this a report or a database workbook? Anyway I suppose an add-in (containing some functions used in some names) is always opened before my problematic workbook.
Because of those two reasons I don't think this (appreciated) link does not help me ....

 

[drsarao]

Before printing a sheet, I do hide much rows, show other rows that were hidden, and change the colors of certain cells;
so when the end-user asks to print, I first save the workbook in the original layout, and after printing the visible rows and the changed colors, I put the workbook back on screen in the original status, so that the enduser gets again what he had before printing...

Just a thought:

Whatever changes you do to the workbook before printing can be undone in VBA itself WITHOUT closing and then opening workbook.

 

[VerlindenG]

I don't think so?
E.g.: I have cells with a blue background, others with a red background, others normal white.
Before printing, I make them all white.
How can I restore the original colors afterwards? I don't think VBA remembers the "original" color?

 

[drsarao]

Look at ActiveWorkbook.CustomViews
You can change view - print - change view back.

ActiveSheet.PageSetup.BlackAndWhite = True will ignore all background colours.

Of course these are just workarounds. But maybe easier to implement.

Your problem maybe actually a bug as pointed in JLGWhiz link.

 

[VerlindenG]

This is very interesting! Thank you.