Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, BC
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding Excel Macros in Excel 97 and 2000 (Excel 2000)

    Hi,

    This is an odd problem that had me wondering if I had lost my mind! It started off as a FoxPro 6 problem, but the culprit is clearly Excel 97 and 2000.

    I have several FoxPro 6 programs that at the end, produce an Excel spreadsheet, and then call an Excel macro to format the spreadsheet, before it gets e-mailed off to wherever. They have always worked just fine. I recently wrote another FoxPro program to do similar things, but this was the first one I had written since our office suite got upgraded from Office 97 to Office 2000 SR-1.

    When I ran this FoxPro program, it would crash on the line that ran the macro. The error message was always the same: "The macro 'Personal.xls!macroname' could not be found. I couldn't figure out why, since I triple checked the macro name, and ensured that it was in my personal.xls file. Everything looked fine, but it would not run. Meanwhile, my older programs ran without a hitch - the macros were called and run.

    At a co-worker's suggestion, I substituted an old Excel macro instead of my new one that was designed to work with my new FoxPro program's output. That macro worked! So why could FoxPro find that macro in my personal.xls and not the new macro?

    I found the answer more or less by accident. My FoxPro program works as far as creating the Excel spreadsheet, it just dies when it comes time to run the macro. So I opened the newly created spreadsheet. It was an Excel 97 spreadsheet, since that is the most recent version that FoxPro 6 creates. When I looked at my list of macros with that open Excel 97 workbook, I saw only about 75% of my macros listed in the personal.xls. Any new macro that I had created since the office upgrade was missing from the list.

    I closed that workbook, and fired up Excel 2000. Doing that, I saw all the macros listed in my personal.xls. For some reason, any macros I've created in Excel 2000 do not show up in the personal.xls when I directly open a "native" Excel 97 workbook. I never noticed this before because the other "missing" macros are not ones that I have had to call from FoxPro.

    So my question is: how can I make ALL my macros available when my system is working on an Excel 97 workbook? Note: I checked to see if I somehow had two personal.xls books (one for each version of Excel), but I did a system search, and I only have the one personal.xls in the xlstart folder.

    Thanks for any help with this stumper!

    John DeA

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

    Re: Finding Excel Macros in Excel 97 and 2000 (Excel 2000)

    I would guess that the personal.xls for each version of Excel is in a different folder, so the macros for Excel 97 and Excel 2000 are being stored in separate files.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, BC
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Excel Macros in Excel 97 and 2000 (Excel 2000)

    Actually no, I checked. There is only one personal.xls file.

    I have noticed one other thing though, and this may make it a FoxPro problem instead. When I open an Excel 97 workbook, I can see all my macros listed in the personal.xls workbook.

    When my FoxPro program runs, it uses the command: oleApp = CREATEOBJECT("Excel.Application") to open an instance of Excel (where oleApp is a variable). When Excel is opened this way, it is "behind the scenes." There is no minimized Excel button on the task bar. If I look at the Windows Task Manager, Excel is not seen to be running on the Applications Tab, but an Excel.exe process is running on the process tab. I'm guessing that FoxPro somehow opens an Excel 97 instance, and this is why the newer Excel 2000 macros are not seen (not recognized, perhaps?).

    When I open an Excel 97 workbook by double-clicking on it when that "invisible" instance of Excel is running, I can not see my new macros. If I close the Excel 97 workbook, then forcibly terminate the Excel process in task manager, then re-open the Excel 97 work book by double clicking it, I can see my new macros. This would seem to point towards FoxPro opening Excel 97 somehow, while any other way opens Excel 2000.

    This is all happening on my work computer. I did not upgrade from Excel 97 to 2000. I simply backed up my documents, macros, etc and IT then wiped my drive and did a fresh install of Office 2000.

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

    Re: Finding Excel Macros in Excel 97 and 2000 (Excel 2000)

    When you start Excel interactively, Personal.xls is loaded, and the macros will work, irrespective of your macro security setting.

    As far as I know, Personal.xls is not loaded when you start Excel by Automation. This has not changed from Excel 97 to Excel 2000 (or 2002).
    If you call a macro Personal.xls!MacroName, Excel needs to open Personal.xls as a normal workbook in order to execute the macros. What happens then, depends on your macro security level. Personal.xls may not load at all, or Excel tries to display a prompt, which is not desirable in Automation, or Personal.xls is loaded without questions.

    What is the macro security level in your Excel 2000?

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, BC
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Excel Macros in Excel 97 and 2000 (Excel 2000)

    Hi Hans,

    Thanks very much for your answer. In my FoxPro program, I do open the personal.xls workbook via a line of code (along with another line to open the workbook I'm going to run the macro on). I checked my Excel 2000 macro security level and it's set to low, which I guess is not surprising since we have a fairly robust corporate anti-virus system here at my job.

    So it seems to be that there is some differentiation between my old Excel macros and my new Excel macros. When FoxPro opens up Excel in Automation, for some reason it can only "see" those macros that I created back when I was using Excel 97. Those ones work in my FoxPro programs when called. Any of the new macros are only visible when I open Excel interactively.

    I suppose a solution would be to upgrade to FoxPro 7 (since I think it can create native Excel 2000 files), but that seems silly just to be able to access these macros. Besides, I know my work won't pop for it.

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

    Re: Finding Excel Macros in Excel 97 and 2000 (Excel 2000)

    I have no idea whether this will work. What happens if you open Excel by using

    Set oleApp = CREATEOBJECT("Excel.Application.9")

    This explicitly calls Excel 2000. Office 97 is version 8, Office 2000 is version 9, Office XP is version 10 and the upcoming Office 2003 is version 11.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, BC
    Posts
    100
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Excel Macros in Excel 97 and 2000 (Excel 2000)

    Hi Hans,

    A belated thank you for your suggestion. I tried it, and alas, no luck. I did finally solve the problem by using the following work around:

    I let FoxPro open Excel 97 automatically, and then allowed the program to crash. With FoxPro minimized, the automatic instance of Excel 97 was still running, so I opened the Excel 97 workbook the program created just before crashing. While that was open, I recreated the same macro I had made in Excel 2000. Then I closed out of everything.

    The result is that my macros now work in FoxPro, but when I try and look at them when running Excel 2000, it's like they are not there. They are invisible. Even though I have only one personal.xls file, I have two sets of macros in it (97 and 2000) and each group can be seen only when its particular flavor of Excel is running. Weird, hmm?

    Thanks again,

    John

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

    Re: Finding Excel Macros in Excel 97 and 2000 (Excel 2000)

    Very weird indeed, but if it works...

Posting Permissions

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