Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    London, Grtr London, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching for Excel Macro (97 to XP)

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

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for Excel Macro (97 to XP)

    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.

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Location
    London, Grtr London, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for Excel Macro (97 to XP)

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Searching for Excel Macro (97 to XP)

    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

  5. #5
    New Lounger
    Join Date
    Jan 2002
    Location
    London, Grtr London, England
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for Excel Macro (97 to XP)

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

    PG

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Searching for Excel Macro (97 to XP)

    Post back if you need any further assistance with coding.

    Andrew C

  7. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Searching for Excel Macro (97 to XP)

    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 <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>. I found that if I moved the spreadsheet with the macros back to the root folder for the project, then all worked well <img src=/S/compute.gif border=0 alt=compute width=40 height=20>. 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. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Ron M <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Searching for Excel Macro (97 to XP)

    See <!post=Personal.xls Tutorial (All) ,118382>Personal.xls Tutorial (All) <!/post> 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).

  9. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Calgary, Alberta, Canada
    Posts
    818
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Searching for Excel Macro (97 to XP)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

    Thanks Hans

    Ron M

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •