Searching for Excel Macro (97 to XP)

Topic

New Reply

I am upgrading from Office 97 to Office XP, I need to search my corporate network to find any spreadsheets that contain macro

Reply | Quote

Replies

Most, but not all, Excel 97 macros will work without change in Excel 2002.

Of those that do not work, some may be easy to convert, whilst others may be #$%#%^#% expensive to convert.

You could write a macro that opened every workbook on your network and determined whether there were any macros, but it would be difficult, if at all possible, to identify in advance which are going to be the troublesome critters.

You’ll need to try them all.

P.S. As an example of the grief. In this forum, sometime over the past few daze, I posted a query about a workbook supplied with the book Microsoft Excel 97 Visual Basic Step By Step. The example in Lesson 12 just does not work in either Excel 2000 or Excel 2002. Actually, I know of a way to rewrite the example, using different techniques, so that it would work in all 3 Excel versions, but this old blockhead wants to find out why the example as presented in the book doesn;t work in Excel 2000 or Excel 2002. Uncle! Actually, this has so frustrated me that a few minutes ago, I ordered a copy of Microsoft Excel 2002 Visual Basic for Applications Step By Step by th same author. Seems like a waste of money to buy the book, but it was a bit under $25(US), so if it answer the issue, it’s well worth it.

Bottom Line: For macros that do not work in later versions, it is sometimes necessary to change the techniques used.

Reply | Quote

Most of my Spreadsheets are going to be tricky little suckers as not only I am moving to Office XP I am moving to Windows XP and from SMS to MSI installer, so in brief, my OS is changing, the location of apps and there specific Dll

Reply | Quote

Microsoft ahve a utility that allows to read/edit file properties without actually opening the files. You can download it from dsofile.exe . Install it and then by setting a reference to it in a VBA Project you can query each xl file in a given folder or drive for the presence of macros.

Have a look at this thread .

Andrew C

Reply | Quote

Thanks for the pointer Andy, due to my lacking of VB, I will give this a crack at work tomorrow.

PG

Reply | Quote

Post back if you need any further assistance with coding.

Andrew C

Reply | Quote

I had a problem related to not having a personal.xls this morning. I had a spreadsheet with macros in it and then I went and re-arranged the folder structure and suddenly the macros were not available to other spreadsheets that needed them . I found that if I moved the spreadsheet with the macros back to the root folder for the project, then all worked well . My questions for this thread are:

1. In Office 2000, where do I put personal.xls for Excel to open on startup?

2. How do I move the macros from existing spreadsheet to the new personal.xls.

Any insight anyone can provide is greatly appreciated. Thanks.

Ron M

Reply | Quote

See Personal.xls Tutorial (All) for instructions how to create Personal.xls.

Once you have done so, you can copy modules and userforms from a workbook to Personal.xls by drag and drop in the Project Explorer in the Visual Basic Editor. You can also copy individual macros by copy and paste. The source workbook must be open for this (Personal.xls is always open).

Reply | Quote

Thanks Hans

Ron M

Reply | Quote