Finding and Updating Files containing VBA

[JTregear]

I am working for an organisation who is in the process of moving a large number of people who are using a variety of versions of MS Office to a new unified infrastructure, using Office 2003.

One of the problems that they know they will have is when users try to run macros which used to work on their old machines, but no longer work in the new environment.

Does anyone have suggestions on utilities or other solutions that would help firstly to identify which documents/spreadsheets have VBA embedded within them. Secondly any recommendations for utilities that will assist in updating old macros so that they will work in the new environment.

Regards

Jonathan

 

[WalkerEvans]

Take a look at the thread "Import all 'bas' files to a new spreadsheet using VBA" (2 August 2007); the posts from PHV and Combo address this general subject. While that thread is aimed at importing code, it could easily be modified to produce a list of files containing code.

As to your second question, the VBA Help file in Office 2003 has an extensive listing of Word Basic -> VBA commands; this is an extremely valuable resource. Unfortunately I know of no utilities that would help; perhaps someone else may be able to suggest something.

----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[combo]

What is the reason that old macros will stop working but the user will be allowed to create new ones working?
I would inform users how to proceed with old files, they should be experienced enough to follow the instructions. Alternatively, it is possible to create an add-in that scans opened files for code or protected vba project, this can trigger a message to the user that the macro should be updated.


combo

 

[Chance1234]

use the Mircosfot scripting object and set up a loop to go through files and folders searching for files with the .xls extenstion

use a reference to the the Microsoft Visual basic for Applications Extensibility object

Theres a few examples here

http://www.cpearson.com/excel/vbe.htm#ListModules

Chance,

Filmmaker, gentleman and ROMAN!

 

[xlbo]

To be honest, VBA tends to be very backwards compatible. I would be surprised if you have many (if any) issues with macros not woprking just because they have moved (from say xl97 or 2000) to 2003. As long as file names are kept and netwrok drives remain mapped to the same locations, I don;t really see why any VBA would fall over....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Chance1234]

both missing file references and things such as

Global pp_Final As New Presentation '(97)

Global pp_Final As Presentation ' (XP)

can be corrected via the use of the VBE object


Chance,

Filmmaker, gentleman and ROMAN!

 

[SkipVought]

"...users try to run macros which used to work on their old machines, but no longer work in the new environment."

WHY?

When a macro aborts, you need to hit the DEBUG button rather than the END button, in order to observe the STATEMENT that the error occured on. That is very important information.

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]