VBA code in Windows XP does not work in Window 7

[handerson0603]

I have received a .xlsm file from a co-worker using XP. The macros (which import data from specified file locations) will not run properly under W7. I get an "Automation error, Unspecified error" popup when I try to run them. The popup opens the VB debugger, but doesn't give any indication of where the code stopped.

I am not a programmer, but can usually read someone else's code and figure out what's going on. The co-worker I got it from has no experience in Windows 7 and no W7 system to try to fix it on. Is there some relatively easy fix for this I can incorporate or communicate to my co-worker?

We are both using Excel 2007.

[patel45]

attach the file, we can test

[handerson0603]

Here is one of the macros (others give the same error), but I don't think there's anything wrong with it. When I try to run it, it stops on the first line. The entire file contains data I don't want to share, and would take some time to scrub.

It has been suggested that there is some issue with permissions across the network, but it stops before I can tell it which files to open, so that doesn't seem likely.

Please Login or Register  to view this content.

[patel45]

I tried som changes, but there are many non defined function.
May be on XP he has some library installed

[BigBas]

There's a chance that you haven't selected certain References in the new version. In the VBE, go to TOOLS-REFERENCES. Look for something like Microsoft Word 11.0 Object Library (it may be different in newer versions, but I don't have on my work computer to test. If it's not checked, give it a check mark and retry.

[handerson0603]

I tried som changes, but there are many non defined function.
May be on XP he has some library installed

That is just a small part of one of the modules in his code. The functions are defined elsewhere. I confirmed that it's nothing in the code by commenting out the entire block except for the Public Sub and End Sub lines. I still get the same error.

There's a chance that you haven't selected certain References in the new version. In the VBE, go to TOOLS-REFERENCES. Look for something like Microsoft Word 11.0 Object Library (it may be different in newer versions, but I don't have on my work computer to test. If it's not checked, give it a check mark and retry.

I compared references with my co-worker and they are identical.

[handerson0603]

I also confirmed that it is not Win7 admin issues... I logged in as admin and get the same errors when I run it.

[patel45]

That is just a small part of one of the modules in his code. The functions are defined elsewhere.

Then how can we test the code ?

[romperstomper]

Can you compile the workbook on Win 7?

[handerson0603]

Then how can we test the code ?

Keep reading ..."I confirmed that it's nothing in the code by commenting out the entire block except for the Public Sub and End Sub lines. I still get the same error." You don't need to test the code because I get an error before it ever executes any of the code.

Edit: I hope this doesn't come across as snarky. I certainly didn't intend it that way, and am grateful for any help you can offer.

---------- Post added at 07:32 AM ---------- Previous post was at 07:30 AM ----------

Can you compile the workbook on Win 7?

As I stated originally, I am not a programmer, so I don't know anything about compiling. If you tell me how (or point me to a resource), I'll try it and let you know the results.

[romperstomper]

Open the VBEditor on the problem machine, and click Debug - Compile VBAProject.

[handerson0603]

Open the VBEditor on the problem machine, and click Debug - Compile VBAProject.

I get the same "Automation error | Unspecified error" as soon as I hit "Compile".

[romperstomper]

It sounds to me as though there is an incompatibility with a library or object used in the workbook. Are you using the same Office version on both machines?

[handerson0603]

Yes, both are using 2007, identically configured by corporate IT.

I probably should have brought this up earlier, but our IT significantly beefed up security when they went to W7. I've had lots of issues doing things that used to be easy on XP, but I've tried all the tricks I have learned so far that have given us problems on other issues.