Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2007 does NOT keep vb library references ...
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Object Library References | Excel Programming | |||
Adding library references programmatically | Excel Programming | |||
Library references | Excel Programming | |||
Object Library References | Excel Programming | |||
Object Library References | Excel Programming |