Excel Reference Not Allowing interface (office 07)

[afguy2011]

I have a macro that works on one computer and not the others (all have office 07 w/SP3). The code basically opens another program, does some calculations, then spits it back into excel. The code works perfectly on another computer. The 2nd line is the line that errors in the first code tag below. (PROGRAMNAME is just the program I am trying to interface with)

Please Login or Register  to view this content.

I have gone to tools>references and used the browse button to define the path to the PROGRAMNAME.exe file. I have even reinstalled and repaired office and the PROGRAMNAME


I have run the following macro to list the Reference names, paths, and reference GUID. Interesting thing is that the file path to the PROGRAMNAME is blank, but a GUID does exist for it.

Please Login or Register  to view this content.

See attachment (excelreference.xlsx) for what this code returns ( I changed all the GUID's to zeros and shortened the paths...) You can see that the path for PROGRAMNAME is blank.


I can't see why the exact same code will run on one computer and not on two others. I have looked into security settings and have "Trust access to the VBA project object model" checked inside the Trust Center> Macro Settings menu

Any help is appreciated!
Thanks in advance

[shg]

Does the VBE show MISSING for the reference on the computers that doen't work?

[afguy2011]

Does the VBE show MISSING for the reference on the computers that doen't work?

No, it does not show MISSING

[shg]

1. Do you set the reference on the machine it doesn't work on, or just depend on it to persist from the workbook where it does work?

2. Can you late-bind instead?

3. What's the program?

[afguy2011]

1. Do you set the reference on the machine it doesn't work on, or just depend on it to persist from the workbook where it does work?

2. Can you late-bind instead?

3. What's the program?

1. Yes, I set the reference on the computers that do not work. I have set, saved, closed, re-opened and re-run the code.

2. I just tried:

Please Login or Register  to view this content.

I don't think it is a late/early binding issue. I just added the reference "Microsoft PowerPoint 12.0 Object Library" and wrote up the following script to test (see Capture1.JPG below). It works fine (even w/o saving/closing/reopening excel). I also don't think it is a late binding issue since it works on the other computer with the same version of excel.

Please Login or Register  to view this content.

Capture1.JPG


3. It's a finite elements engineering software

I really think it has to do with the fact that a file path is not specified inside the reference for my finite elements program. If I run the "Sub ListReferencePaths()" from post #1 after my test, the path for powerpoint is what it should be: C:\Program Files (x86)\Microsoft Office\Office12\MSPPT.OLB

If you look at Capture1.JPG, this same file path is shown for the powerpoint checked reference. Strange thing is that the correct path is also shown for my finite elements program, but does not get called when the "Sub ListReferencePaths()" code is run...

Is there any way to manually enter the file path without using the "Browse" button?

Thanks for your time. I appreciate it!

[shg]

I have no suggestion, but will ask some people smarter than me to look in.

[romperstomper]

The 2nd line is the line that errors in the first code tag below. (PROGRAMNAME is just the program I am trying to interface with)

Please Login or Register  to view this content.

The fact that the second line is the one that errors and not the first would imply that the issue is not to do with references per se. A few questions:
1. Can the other machines manually run the application in question?
2. Was it installed for all users on the problem machines?
3. What is the error? 'ActiveX can't create object'? Other?

[afguy2011]

I have no suggestion, but will ask some people smarter than me to look in.

Thanks, I appreciate it!

The fact that the second line is the one that errors and not the first would imply that the issue is not to do with references per se. A few questions:
1. Can the other machines manually run the application in question?
2. Was it installed for all users on the problem machines?
3. What is the error? 'ActiveX can't create object'? Other?

1. & 2. - Yes, I am able to open and run the program with any user on the machines that don't work.
3. The error is the 'ActiveX can't create object'. See Capture2.jpg


Thanks everyone for there time!

[romperstomper]

Is there a .tlb or .olb file that you can set a reference to rather than the .exe file, as a matter of interest?

You said you had already reinstalled the program in question?

Do you have User Account Control turned on in the OS?

Have there been any updates applied to the failing machines and not to the working machines?

(This isn't really anything to do with Excel, by the way, but it's an interesting problem )

[afguy2011]

Is there a .tlb or .olb file that you can set a reference to rather than the .exe file, as a matter of interest?

You said you had already reinstalled the program in question?

Do you have User Account Control turned on in the OS?

Have there been any updates applied to the failing machines and not to the working machines?

(This isn't really anything to do with Excel, by the way, but it's an interesting problem )

There is no .tlb or .olb file to reference. I did try re-install the both excel and the finite elements program. User Account Control is turned on. The machine that works has received the same updates as the ones that don't work.

I did try running the "Sub ListReferencePaths()" from post #1 on the machine that works. Surprisingly, it did not pull any information into the spreadsheet. This might be because I wasn't logged in under admin. I will run "Sub ListReferencePaths()" again tomorrow when I can get admin credentials. hopefully that will give me a path to a file other than the .exe file...

Any other suggestions are welcome.

Thanks for the continued help!

[romperstomper]

That sub should not require admin rights to run.

Can you turn UAC off?

I presume the program is actually installed on all machines locally, not accessed via terminal server or Citrix etc?

[afguy2011]

GOT IT!!!!

I knew it was something simple... When I installed the FEA program, I should have right clicked on the "setup.exe" file and clicked on "Run as Administrator"

Without doing this, the program could not make the necessary registry changes/additions it needed to interface with excel...

Thanks everyone again for there time and help!