Hello out there in Excel programming land ...

I am looking to upgrade (migrate) a set of Excel workbooks from 2003
to 2007.

The basic concept is that there is a single workbook (called say
MACRO.xls) that contains all VBA code (function, procedures, form,
etc.) which is ALWAYS open on the users pc, this way any file the user
is working on (called say USER.xls) can easily access the necessary
code/information as it is linked to MACRO.xls via a VBA reference.

This does not seem to work in Excel 2007, assuming that my macro
security is set to Medium and USER.xls is a file previously created ni
Excel2003, if I do the following (which is long winded, but
demonstrates my point) :-

1) Open MACRO.xls (usual warning messages about enabling macros in a
workbook)
2) Open USER.xls (no warnings as this file does NOT contain any
code)
3) Go to visual basic and check out the references, there is no
reference in USER.xls to MACRO.xls (which there was previously in
Excel 20003)
4) So I put back in the reference to MACRO.xls.
5) Save USER as a .xlsx, .xlsm, .xlsb file. I should be only saving
as a .xlsx file as USER contains NO code.
6) Open each of the USER spreadsheets in turn, check out visual
basic and the reference to MACRO.xls has been removed.

Currently I have hundreds of Excel 2003 workbooks that contain NO code
BUT have a reference to another Excel workbook, if the above is true
then NONE of these spreadsheets will function any more.


There is a solution.


But not a good one.



If after step 4), I insert a module into the USER workbook and save as
a .xlsm file (because it NOW contains code). NOW the reference to
MACRO.xls is still present whenever I open the USER file.
This is is a impratical solution as this would mean inserting an empty
module into every one of my current spreadsheets.

I cannot help thinking that I am missing some simple point in this
process.

Can anyone help


Sean


P.S. I just realised that the above could be a bit confusing, so here
is a simpler example.
1) Create a blank workbook in Excel 2007
2) In visual basic add a reference to anything (say "Microsoft
Powerpoint 12.0 object library")
3) Save the work as a .xlsm workbook and close it
4) Re-open the workbook
5) Check your references in visual basic and you will see that
the powerpoint reference has disappeared
,,,
6) Repeat but after step 2) insert a blank module into your
workbook, now all works fine

Why would you need a reference to PowerPoint in an Excel workbook without any
code?

Thanks,
Barb Reinhardt



"Sean" wrote:

Hello out there in Excel programming land ...

I am looking to upgrade (migrate) a set of Excel workbooks from 2003
to 2007.

The basic concept is that there is a single workbook (called say
MACRO.xls) that contains all VBA code (function, procedures, form,
etc.) which is ALWAYS open on the users pc, this way any file the user
is working on (called say USER.xls) can easily access the necessary
code/information as it is linked to MACRO.xls via a VBA reference.

This does not seem to work in Excel 2007, assuming that my macro
security is set to Medium and USER.xls is a file previously created ni
Excel2003, if I do the following (which is long winded, but
demonstrates my point) :-

1) Open MACRO.xls (usual warning messages about enabling macros in a
workbook)
2) Open USER.xls (no warnings as this file does NOT contain any
code)
3) Go to visual basic and check out the references, there is no
reference in USER.xls to MACRO.xls (which there was previously in
Excel 20003)
4) So I put back in the reference to MACRO.xls.
5) Save USER as a .xlsx, .xlsm, .xlsb file. I should be only saving
as a .xlsx file as USER contains NO code.
6) Open each of the USER spreadsheets in turn, check out visual
basic and the reference to MACRO.xls has been removed.

Currently I have hundreds of Excel 2003 workbooks that contain NO code
BUT have a reference to another Excel workbook, if the above is true
then NONE of these spreadsheets will function any more.


There is a solution.


But not a good one.



If after step 4), I insert a module into the USER workbook and save as
a .xlsm file (because it NOW contains code). NOW the reference to
MACRO.xls is still present whenever I open the USER file.
This is is a impratical solution as this would mean inserting an empty
module into every one of my current spreadsheets.

I cannot help thinking that I am missing some simple point in this
process.

Can anyone help


Sean


P.S. I just realised that the above could be a bit confusing, so here
is a simpler example.
1) Create a blank workbook in Excel 2007
2) In visual basic add a reference to anything (say "Microsoft
Powerpoint 12.0 object library")
3) Save the work as a .xlsm workbook and close it
4) Re-open the workbook
5) Check your references in visual basic and you will see that
the powerpoint reference has disappeared
,,,
6) Repeat but after step 2) insert a blank module into your
workbook, now all works fine

Hi Sean,

This appears to be a bug in Excel 2007 that has caused quite a bit of
pain in my projects as well. The only good solution I've come up with is to
add an empty module to the workbooks that contain only references to other
workbooks. If you do this, Excel 2007 will leave the references alone.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Sean" wrote in message
...
Hello out there in Excel programming land ...

I am looking to upgrade (migrate) a set of Excel workbooks from 2003
to 2007.

The basic concept is that there is a single workbook (called say
MACRO.xls) that contains all VBA code (function, procedures, form,
etc.) which is ALWAYS open on the users pc, this way any file the user
is working on (called say USER.xls) can easily access the necessary
code/information as it is linked to MACRO.xls via a VBA reference.

This does not seem to work in Excel 2007, assuming that my macro
security is set to Medium and USER.xls is a file previously created ni
Excel2003, if I do the following (which is long winded, but
demonstrates my point) :-

1) Open MACRO.xls (usual warning messages about enabling macros in a
workbook)
2) Open USER.xls (no warnings as this file does NOT contain any
code)
3) Go to visual basic and check out the references, there is no
reference in USER.xls to MACRO.xls (which there was previously in
Excel 20003)
4) So I put back in the reference to MACRO.xls.
5) Save USER as a .xlsx, .xlsm, .xlsb file. I should be only saving
as a .xlsx file as USER contains NO code.
6) Open each of the USER spreadsheets in turn, check out visual
basic and the reference to MACRO.xls has been removed.

Currently I have hundreds of Excel 2003 workbooks that contain NO code
BUT have a reference to another Excel workbook, if the above is true
then NONE of these spreadsheets will function any more.


There is a solution.


But not a good one.



If after step 4), I insert a module into the USER workbook and save as
a .xlsm file (because it NOW contains code). NOW the reference to
MACRO.xls is still present whenever I open the USER file.
This is is a impratical solution as this would mean inserting an empty
module into every one of my current spreadsheets.

I cannot help thinking that I am missing some simple point in this
process.

Can anyone help


Sean


P.S. I just realised that the above could be a bit confusing, so here
is a simpler example.
1) Create a blank workbook in Excel 2007
2) In visual basic add a reference to anything (say "Microsoft
Powerpoint 12.0 object library")
3) Save the work as a .xlsm workbook and close it
4) Re-open the workbook
5) Check your references in visual basic and you will see that
the powerpoint reference has disappeared
,,,
6) Repeat but after step 2) insert a blank module into your
workbook, now all works fine

Rob,

Under what circumstances would you have references in a workbook without code?

Thanks,
Barb Reinhardt



"Rob Bovey" wrote:

Hi Sean,

This appears to be a bug in Excel 2007 that has caused quite a bit of
pain in my projects as well. The only good solution I've come up with is to
add an empty module to the workbooks that contain only references to other
workbooks. If you do this, Excel 2007 will leave the references alone.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Sean" wrote in message
...
Hello out there in Excel programming land ...

I am looking to upgrade (migrate) a set of Excel workbooks from 2003
to 2007.

The basic concept is that there is a single workbook (called say
MACRO.xls) that contains all VBA code (function, procedures, form,
etc.) which is ALWAYS open on the users pc, this way any file the user
is working on (called say USER.xls) can easily access the necessary
code/information as it is linked to MACRO.xls via a VBA reference.

This does not seem to work in Excel 2007, assuming that my macro
security is set to Medium and USER.xls is a file previously created ni
Excel2003, if I do the following (which is long winded, but
demonstrates my point) :-

1) Open MACRO.xls (usual warning messages about enabling macros in a
workbook)
2) Open USER.xls (no warnings as this file does NOT contain any
code)
3) Go to visual basic and check out the references, there is no
reference in USER.xls to MACRO.xls (which there was previously in
Excel 20003)
4) So I put back in the reference to MACRO.xls.
5) Save USER as a .xlsx, .xlsm, .xlsb file. I should be only saving
as a .xlsx file as USER contains NO code.
6) Open each of the USER spreadsheets in turn, check out visual
basic and the reference to MACRO.xls has been removed.

Currently I have hundreds of Excel 2003 workbooks that contain NO code
BUT have a reference to another Excel workbook, if the above is true
then NONE of these spreadsheets will function any more.


There is a solution.


But not a good one.



If after step 4), I insert a module into the USER workbook and save as
a .xlsm file (because it NOW contains code). NOW the reference to
MACRO.xls is still present whenever I open the USER file.
This is is a impratical solution as this would mean inserting an empty
module into every one of my current spreadsheets.

I cannot help thinking that I am missing some simple point in this
process.

Can anyone help


Sean


P.S. I just realised that the above could be a bit confusing, so here
is a simpler example.
1) Create a blank workbook in Excel 2007
2) In visual basic add a reference to anything (say "Microsoft
Powerpoint 12.0 object library")
3) Save the work as a .xlsm workbook and close it
4) Re-open the workbook
5) Check your references in visual basic and you will see that
the powerpoint reference has disappeared
,,,
6) Repeat but after step 2) insert a blank module into your
workbook, now all works fine

Hi Ron,

No, I haven't had time. Too busy working on PED2. Go ahead and file it
and send me the link and I'll vote five stars. <g

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Ron de Bruin" wrote in message
...
Hi Rob

Do you report this nasty bug ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rob Bovey" wrote in message
...
Hi Barb,

When building Excel applications it's a good idea to completely
separate your code from your user interface. It makes things much easier
to maintain. Since the user interface workbook is therefore pretty
useless by itself, you want to force its associated add-in open any time
it opens.

A very easy and reliable way to do this with no code is to set a
reference from the UI workbook to the add-in. When the UI workbook opens,
the reference forces the add-in open. Excel 2007 breaks this solution.
I've also experienced forms controls on UI worksheets that have been
assigned to macros in an add-in not working in Excel 2007 until I add a
dummy module.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Barb Reinhardt" wrote in
message ...
Rob,

Under what circumstances would you have references in a workbook without
code?

Thanks,
Barb Reinhardt



"Rob Bovey" wrote:

Hi Sean,

This appears to be a bug in Excel 2007 that has caused quite a bit
of
pain in my projects as well. The only good solution I've come up with
is to
add an empty module to the workbooks that contain only references to
other
workbooks. If you do this, Excel 2007 will leave the references alone.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Sean" wrote in message
...
Hello out there in Excel programming land ...

I am looking to upgrade (migrate) a set of Excel workbooks from 2003
to 2007.

The basic concept is that there is a single workbook (called say
MACRO.xls) that contains all VBA code (function, procedures, form,
etc.) which is ALWAYS open on the users pc, this way any file the
user
is working on (called say USER.xls) can easily access the necessary
code/information as it is linked to MACRO.xls via a VBA reference.

This does not seem to work in Excel 2007, assuming that my macro
security is set to Medium and USER.xls is a file previously created
ni
Excel2003, if I do the following (which is long winded, but
demonstrates my point) :-

1) Open MACRO.xls (usual warning messages about enabling macros in
a
workbook)
2) Open USER.xls (no warnings as this file does NOT contain any
code)
3) Go to visual basic and check out the references, there is no
reference in USER.xls to MACRO.xls (which there was previously in
Excel 20003)
4) So I put back in the reference to MACRO.xls.
5) Save USER as a .xlsx, .xlsm, .xlsb file. I should be only saving
as a .xlsx file as USER contains NO code.
6) Open each of the USER spreadsheets in turn, check out visual
basic and the reference to MACRO.xls has been removed.

Currently I have hundreds of Excel 2003 workbooks that contain NO
code
BUT have a reference to another Excel workbook, if the above is true
then NONE of these spreadsheets will function any more.


There is a solution.


But not a good one.



If after step 4), I insert a module into the USER workbook and save
as
a .xlsm file (because it NOW contains code). NOW the reference to
MACRO.xls is still present whenever I open the USER file.
This is is a impratical solution as this would mean inserting an
empty
module into every one of my current spreadsheets.

I cannot help thinking that I am missing some simple point in this
process.

Can anyone help


Sean


P.S. I just realised that the above could be a bit confusing, so here
is a simpler example.
1) Create a blank workbook in Excel 2007
2) In visual basic add a reference to anything (say "Microsoft
Powerpoint 12.0 object library")
3) Save the work as a .xlsm workbook and close it
4) Re-open the workbook
5) Check your references in visual basic and you will see that
the powerpoint reference has disappeared
,,,
6) Repeat but after step 2) insert a blank module into your
workbook, now all works fine

Hi Rob

When Connect is online again(I have problems the last days)
I will send the bug for you (you are lazy<vbg)

Have a great day



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rob Bovey" wrote in message ...
Hi Ron,

No, I haven't had time. Too busy working on PED2. Go ahead and file it
and send me the link and I'll vote five stars. <g

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Ron de Bruin" wrote in message
...
Hi Rob

Do you report this nasty bug ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Rob Bovey" wrote in message
...
Hi Barb,

When building Excel applications it's a good idea to completely
separate your code from your user interface. It makes things much easier
to maintain. Since the user interface workbook is therefore pretty
useless by itself, you want to force its associated add-in open any time
it opens.

A very easy and reliable way to do this with no code is to set a
reference from the UI workbook to the add-in. When the UI workbook opens,
the reference forces the add-in open. Excel 2007 breaks this solution.
I've also experienced forms controls on UI worksheets that have been
assigned to macros in an add-in not working in Excel 2007 until I add a
dummy module.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Barb Reinhardt" wrote in
message ...
Rob,

Under what circumstances would you have references in a workbook without
code?

Thanks,
Barb Reinhardt



"Rob Bovey" wrote:

Hi Sean,

This appears to be a bug in Excel 2007 that has caused quite a bit
of
pain in my projects as well. The only good solution I've come up with
is to
add an empty module to the workbooks that contain only references to
other
workbooks. If you do this, Excel 2007 will leave the references alone.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Sean" wrote in message
...
Hello out there in Excel programming land ...

I am looking to upgrade (migrate) a set of Excel workbooks from 2003
to 2007.

The basic concept is that there is a single workbook (called say
MACRO.xls) that contains all VBA code (function, procedures, form,
etc.) which is ALWAYS open on the users pc, this way any file the
user
is working on (called say USER.xls) can easily access the necessary
code/information as it is linked to MACRO.xls via a VBA reference.

This does not seem to work in Excel 2007, assuming that my macro
security is set to Medium and USER.xls is a file previously created
ni
Excel2003, if I do the following (which is long winded, but
demonstrates my point) :-

1) Open MACRO.xls (usual warning messages about enabling macros in
a
workbook)
2) Open USER.xls (no warnings as this file does NOT contain any
code)
3) Go to visual basic and check out the references, there is no
reference in USER.xls to MACRO.xls (which there was previously in
Excel 20003)
4) So I put back in the reference to MACRO.xls.
5) Save USER as a .xlsx, .xlsm, .xlsb file. I should be only saving
as a .xlsx file as USER contains NO code.
6) Open each of the USER spreadsheets in turn, check out visual
basic and the reference to MACRO.xls has been removed.

Currently I have hundreds of Excel 2003 workbooks that contain NO
code
BUT have a reference to another Excel workbook, if the above is true
then NONE of these spreadsheets will function any more.


There is a solution.


But not a good one.



If after step 4), I insert a module into the USER workbook and save
as
a .xlsm file (because it NOW contains code). NOW the reference to
MACRO.xls is still present whenever I open the USER file.
This is is a impratical solution as this would mean inserting an
empty
module into every one of my current spreadsheets.

I cannot help thinking that I am missing some simple point in this
process.

Can anyone help


Sean


P.S. I just realised that the above could be a bit confusing, so here
is a simpler example.
1) Create a blank workbook in Excel 2007
2) In visual basic add a reference to anything (say "Microsoft
Powerpoint 12.0 object library")
3) Save the work as a .xlsm workbook and close it
4) Re-open the workbook
5) Check your references in visual basic and you will see that
the powerpoint reference has disappeared
,,,
6) Repeat but after step 2) insert a blank module into your
workbook, now all works fine

"Ron de Bruin" wrote in message
...
Hi Rob

When Connect is online again(I have problems the last days)
I will send the bug for you (you are lazy<vbg)

You don't have to tell me, my wife already handles that. <g

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

I doubt that MS goes to consider this as a bug . I bet that they will
say that this is a feature in 2007 and in all future versions.


On Aug 28, 4:24*pm, "Ron de Bruin" wrote:
Hi Rob

Do you report this nasty bug ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Rob Bovey" wrote in . ..
Hi Barb,

* *When buildingExcelapplications it's a good idea to completely separate
your code from your user interface. It makes things much easier to maintain.
Since the user interface workbook is therefore pretty useless by itself, you
want to force its associated add-in open any time it opens.

* *A very easy and reliable way to do this with no code is to set a
reference from the UI workbook to the add-in. When the UI workbook opens,
the reference forces the add-in open.Excel2007breaks this solution. I've
also experienced forms controls on UI worksheets that have been assigned to
macros in an add-in not working inExcel2007until I add a dummy module.

--
Rob Bovey,ExcelMVP
Application Professionals
http://www.appspro.com/

* Take yourExceldevelopment skills to the next level.
* ProfessionalExcelDevelopment
http://www.appspro.com/Books/Books.htm

"Barb Reinhardt" wrote in message
...
Rob,

Under what circumstances would you have references in a workbook without
code?

Thanks,
Barb Reinhardt

"Rob Bovey" wrote:

Hi Sean,

* * This appears to be a bug inExcel2007that has caused quite a bit of
pain in my projects as well. The only good solution I've come up with is
to
add an empty module to the workbooks that contain only references to
other
workbooks. If you do this,Excel2007will leave the references alone.

--
Rob Bovey,ExcelMVP
Application Professionals
http://www.appspro.com/

* Take yourExceldevelopment skills to the next level.
* ProfessionalExcelDevelopment
http://www.appspro.com/Books/Books.htm

"Sean" wrote in message
....
Hello out there inExcelprogramming land ...

I am looking to upgrade (migrate) a set ofExcelworkbooks from 2003
to2007.

The basic concept is that there is a single workbook (called say
MACRO.xls) that contains all VBA code (function, procedures, form,
etc.) which is ALWAYS open on the users pc, this way any file the user
is working on (called say USER.xls) can easily access the necessary
code/information as it is linked to MACRO.xls via a VBA reference.

This does not seem to work inExcel2007, assuming that my macro
security is set to Medium and USER.xls is a file previously created ni
Excel2003, if I do the following (which is long winded, but
demonstrates my point) :-

1) * Open MACRO.xls (usual warning messages about enabling macros in a
workbook)
2) * Open USER.xls (no warnings as this file does NOT contain any
code)
3) * Go to visual basic and check out the references, there is no
reference in USER.xls to MACRO.xls (which there was previously in
Excel20003)
4) * So I put back in the reference to MACRO.xls.
5) * Save USER as a .xlsx, .xlsm, .xlsb file. I should be only saving
as a .xlsx file as USER contains NO code.
6) * Open each of the USER spreadsheets in turn, check out visual
basic and the reference to MACRO.xls has been removed.

Currently I have hundreds ofExcel2003 workbooks that contain NO code
BUT have a reference to anotherExcelworkbook, if the above is true
then NONE of these spreadsheets will function any more.

There is a solution.

But not a good one.

If after step 4), I insert a module into the USER workbook and save as
a .xlsm file (because it NOW contains code). NOW the reference to
MACRO.xls is still present whenever I open the USER file.
This is is a impratical solution as this would mean inserting an empty
module into every one of my current spreadsheets.

I cannot help thinking that I am missing some simple point in this
process.

Can anyone help

Sean

P.S. I just realised that the above could be a bit confusing, so here
is a simpler example.
* *1) * Create a blank workbook inExcel2007
* *2) * In visual basic add a reference to anything (say "Microsoft
Powerpoint 12.0 object library")
* *3) * Save the work as a .xlsm workbook and close it
* *4) * Re-open the workbook
* *5) * Check your references in visual basic and you will see that
the powerpoint reference has disappeared
* ,,,
* *6) * Repeat but after step 2) insert a blank module into your
workbook, now all works fine- Hide quoted text -

- Show quoted text -

Hi Sean

Update:

This was actually a security decision that we made - not to persist the VBA project (which includes references) - if there wasn't
any code/modules in the project, since just persisting an loading the VBA project itself increases the attack surface of the
product.


We can make a macro that open each file in a folder and insert a empty module if you want ?
Maybe I will create a page about it

Let me know if you want to try a macro


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sean" wrote in message ...
Hello out there in Excel programming land ...

I am looking to upgrade (migrate) a set of Excel workbooks from 2003
to 2007.

The basic concept is that there is a single workbook (called say
MACRO.xls) that contains all VBA code (function, procedures, form,
etc.) which is ALWAYS open on the users pc, this way any file the user
is working on (called say USER.xls) can easily access the necessary
code/information as it is linked to MACRO.xls via a VBA reference.

This does not seem to work in Excel 2007, assuming that my macro
security is set to Medium and USER.xls is a file previously created ni
Excel2003, if I do the following (which is long winded, but
demonstrates my point) :-

1) Open MACRO.xls (usual warning messages about enabling macros in a
workbook)
2) Open USER.xls (no warnings as this file does NOT contain any
code)
3) Go to visual basic and check out the references, there is no
reference in USER.xls to MACRO.xls (which there was previously in
Excel 20003)
4) So I put back in the reference to MACRO.xls.
5) Save USER as a .xlsx, .xlsm, .xlsb file. I should be only saving
as a .xlsx file as USER contains NO code.
6) Open each of the USER spreadsheets in turn, check out visual
basic and the reference to MACRO.xls has been removed.

Currently I have hundreds of Excel 2003 workbooks that contain NO code
BUT have a reference to another Excel workbook, if the above is true
then NONE of these spreadsheets will function any more.


There is a solution.


But not a good one.



If after step 4), I insert a module into the USER workbook and save as
a .xlsm file (because it NOW contains code). NOW the reference to
MACRO.xls is still present whenever I open the USER file.
This is is a impratical solution as this would mean inserting an empty
module into every one of my current spreadsheets.

I cannot help thinking that I am missing some simple point in this
process.

Can anyone help


Sean


P.S. I just realised that the above could be a bit confusing, so here
is a simpler example.
1) Create a blank workbook in Excel 2007
2) In visual basic add a reference to anything (say "Microsoft
Powerpoint 12.0 object library")
3) Save the work as a .xlsm workbook and close it
4) Re-open the workbook
5) Check your references in visual basic and you will see that
the powerpoint reference has disappeared
,,,
6) Repeat but after step 2) insert a blank module into your
workbook, now all works fine

Dear Rob (or should it be Ron?)

Firstly thank you to you both for confirming my suspicions regarding
the solution to my problem.

I might try to write the code to insert a module into a spreadsheet
(I've never done any coding that CREATES code) so it should be an
interesting experience. The only real problem is that the files that
will need updating are in many directories on many volumes, so being
able to change them on mass would involve spending more time
identifying the files than actually "correcting" them, but that's my
problem.

Honestly, I am a bit disappointed with this result as it means a minor
change to a major number of files for a reason I find quite
unjustifiable. I would however be interested in Microsoft's official
response to this issue (I'm assuming this was the gist of your
discussion with Rob).

Thanks again boys, and if I create a useful solution I'll post the
code here


Sean.



On Aug 30, 11:18*pm, "Ron de Bruin" wrote:
Hi Sean

Update:

This was actually a security decision that we made - not to persist the VBA project (which includes references) - if there wasn't
any code/modules in the project, since just persisting an loading the VBA project itself increases the attack surface of the
product.

We can make a macro that open each file in a folder and insert a empty module if you want ?
Maybe I will create a page about it

Let me know if you want to try a macro

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Sean" wrote in ...
Hello out there in Excel programming land ...

I am looking to upgrade (migrate) a set of Excel workbooks from 2003
to 2007.

The basic concept is that there is a single workbook (called say
MACRO.xls) that contains all VBA code (function, procedures, form,
etc.) which is ALWAYS open on the users pc, this way any file the user
is working on (called say USER.xls) can easily access the necessary
code/information as it is linked to MACRO.xls via a VBA reference.

This does not seem to work in Excel 2007, assuming that my macro
security is set to Medium and USER.xls is a file previously created ni
Excel2003, if I do the following (which is long winded, but
demonstrates my point) :-

1) * Open MACRO.xls (usual warning messages about enabling macros in a
workbook)
2) * Open USER.xls (no warnings as this file does NOT contain any
code)
3) * Go to visual basic and check out the references, there is no
reference in USER.xls to MACRO.xls (which there was previously in
Excel 20003)
4) * So I put back in the reference to MACRO.xls.
5) * Save USER as a .xlsx, .xlsm, .xlsb file. I should be only saving
as a .xlsx file as USER contains NO code.
6) * Open each of the USER spreadsheets in turn, check out visual
basic and the reference to MACRO.xls has been removed.

Currently I have hundreds of Excel 2003 workbooks that contain NO code
BUT have a reference to another Excel workbook, if the above is true
then NONE of these spreadsheets will function any more.

There is a solution.

But not a good one.

If after step 4), I insert a module into the USER workbook and save as
a .xlsm file (because it NOW contains code). NOW the reference to
MACRO.xls is still present whenever I open the USER file.
This is is a impratical solution as this would mean inserting an empty
module into every one of my current spreadsheets.

I cannot help thinking that I am missing some simple point in this
process.

Can anyone help

Sean

P.S. I just realised that the above could be a bit confusing, so here
is a simpler example.
* *1) * Create a blank workbook in Excel 2007
* *2) * In visual basic add a reference to anything (say "Microsoft
Powerpoint 12.0 object library")
* *3) * Save the work as a .xlsm workbook and close it
* *4) * Re-open the workbook
* *5) * Check your references in visual basic and you will see that
the powerpoint reference has disappeared
* ,,,
* *6) * Repeat but after step 2) insert a blank module into your
workbook, now all works fine- Hide quoted text -

- Show quoted text -

I would however be interested in Microsoft's official response to this issue
You read it

This was actually a security decision that we made - not to persist the VBA project (which includes references) - if there wasn't
any code/modules in the project, since just persisting an loading the VBA project itself increases the attack surface of the
product.

If you need help with the macro let me know



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sean" wrote in message ...
Dear Rob (or should it be Ron?)

Firstly thank you to you both for confirming my suspicions regarding
the solution to my problem.

I might try to write the code to insert a module into a spreadsheet
(I've never done any coding that CREATES code) so it should be an
interesting experience. The only real problem is that the files that
will need updating are in many directories on many volumes, so being
able to change them on mass would involve spending more time
identifying the files than actually "correcting" them, but that's my
problem.

Honestly, I am a bit disappointed with this result as it means a minor
change to a major number of files for a reason I find quite
unjustifiable. I would however be interested in Microsoft's official
response to this issue (I'm assuming this was the gist of your
discussion with Rob).

Thanks again boys, and if I create a useful solution I'll post the
code here


Sean.



On Aug 30, 11:18 pm, "Ron de Bruin" wrote:
Hi Sean

Update:

This was actually a security decision that we made - not to persist the VBA project (which includes references) - if there wasn't
any code/modules in the project, since just persisting an loading the VBA project itself increases the attack surface of the
product.

We can make a macro that open each file in a folder and insert a empty module if you want ?
Maybe I will create a page about it

Let me know if you want to try a macro

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Sean" wrote in ...
Hello out there in Excel programming land ...

I am looking to upgrade (migrate) a set of Excel workbooks from 2003
to 2007.

The basic concept is that there is a single workbook (called say
MACRO.xls) that contains all VBA code (function, procedures, form,
etc.) which is ALWAYS open on the users pc, this way any file the user
is working on (called say USER.xls) can easily access the necessary
code/information as it is linked to MACRO.xls via a VBA reference.

This does not seem to work in Excel 2007, assuming that my macro
security is set to Medium and USER.xls is a file previously created ni
Excel2003, if I do the following (which is long winded, but
demonstrates my point) :-

1) Open MACRO.xls (usual warning messages about enabling macros in a
workbook)
2) Open USER.xls (no warnings as this file does NOT contain any
code)
3) Go to visual basic and check out the references, there is no
reference in USER.xls to MACRO.xls (which there was previously in
Excel 20003)
4) So I put back in the reference to MACRO.xls.
5) Save USER as a .xlsx, .xlsm, .xlsb file. I should be only saving
as a .xlsx file as USER contains NO code.
6) Open each of the USER spreadsheets in turn, check out visual
basic and the reference to MACRO.xls has been removed.

Currently I have hundreds of Excel 2003 workbooks that contain NO code
BUT have a reference to another Excel workbook, if the above is true
then NONE of these spreadsheets will function any more.

There is a solution.

But not a good one.

If after step 4), I insert a module into the USER workbook and save as
a .xlsm file (because it NOW contains code). NOW the reference to
MACRO.xls is still present whenever I open the USER file.
This is is a impratical solution as this would mean inserting an empty
module into every one of my current spreadsheets.

I cannot help thinking that I am missing some simple point in this
process.

Can anyone help

Sean

P.S. I just realised that the above could be a bit confusing, so here
is a simpler example.
1) Create a blank workbook in Excel 2007
2) In visual basic add a reference to anything (say "Microsoft
Powerpoint 12.0 object library")
3) Save the work as a .xlsm workbook and close it
4) Re-open the workbook
5) Check your references in visual basic and you will see that
the powerpoint reference has disappeared
,,,
6) Repeat but after step 2) insert a blank module into your
workbook, now all works fine- Hide quoted text -

- Show quoted text -