Solved

An old Excel / VBA script stopped working after upgrade...

Posted on 2011-03-21
24
1,006 Views
Last Modified: 2012-05-11
First of all, I'm a rank novice and this was my very first VBA application.  It worked great a year ago, then I upgraded from XP to Windows 7, and from Office 2003 to Office 2010, and from Photoshop CS2 to CS5.  (All programs are involved.)  The program no longer works; and I can't figure out what the problem is.

This program was designed to add images to my stock photography website.  Once a tweaked picture was opened in Photoshop, I would open this XL spreadsheet with a button labeled "Add to FA website".  Among other things the program would ask questions about location, keywords, title, and then it would add a logo to the image, shrink and sharpen to two different sizes (depending upon image orientation) and save them in the right locations, create a new HTML page and save that to the right location, and finally add a linked thumbnail image to a master index.htm page.  All of this worked flawlessly for a couple of years.

Today after the aforementioned upgrades the program asks me what subdirectory to save it in, opens a blank Microsoft Word document (as it should), and then it hangs for about 20 seconds, after which it errors out with "Runtime error '429': ActiveX component can't create object".  When I hit debug the highlighted line is "set objapp = CreateObject("Photoshop.Application").

I'm aware that the API for Photoshop CS5 changed somewhat when it came to printing an image; however none of that was invoked by my program.

I have a feeling this involves having to register an ActiveX DLL or an OCX, but I have no idea what nor any idea of how to do it. (And please don't laugh at my code - as I said this was my first project.)  Something tells me this will be painfully obvious to most of you.  Any insights welcome!

(Note: I couldn't find a 'VBA' zone to classify this in, so I chose the next closest thing.)


Add-New-Images-v11.xls
0
Comment
Question by:Garyfriedman2005
  • 12
  • 10
  • 2
24 Comments
 

Author Comment

by:Garyfriedman2005
Comment Utility
More info: I checked the TOOLS --> REFERENCES window, and noticed that libraries for Photoshop 8 and 9 were checked; but libraries for Photoshop CS5 were not.  So I unchecked the Photoshop 8 and 9 and checked both the CS5 libraries, but when I hit "OK" I instantly got a "Error in loading DLL" error message.

Now what?  Am I on the right track?
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Are you running 32 bit or 64 bit CS5? You are most likely running 32 bit Excel, so I can understand the ActiveX error if you use 64 bit CS5.
0
 

Author Comment

by:Garyfriedman2005
Comment Utility
Excellent question!  64 bit CS5, and I'm not sure which version of Office 2010 I'm running.

Assuming you're right (32-bit Office accessing 64-bit Photoshop); what's the best way to proceed?

-Gary
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
In one of the Office applications, open the File...Help menu item. It should let you know whether you are running 32 bit Office or not.

I believe that you need to have both Excel and Photoshop using either the 32-bit or the 64-bit versions (both the same). This belief is supported by http://forums.adobe.com/thread/707073?tstart=1

Unfortunately for you, I have now reached the limits of my knowledge. And since I don't have Photoshop on my computer, I can't even test possible solutions. I can ask for additional assistance if you can confirm the diagnosis of the problem.

Brad
0
 

Author Comment

by:Garyfriedman2005
Comment Utility
I have verified that I do indeed have 32-bit version of Office 2010 installed on my 64-bit Windows 7.

But I also have installed the 32-bit Photoshop CS2, and I believe that's what the Adobe Photoshop 9.0 Object Library and PHotoshop 8.0 type library (both of which are checked) refer to.  So should this all not still work after all?
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
I believe that CreateObject is finding the 64-bit CS5 version of Photoshop and finding an incompatibility with 32-bit Excel. That seems to be the substance of the Adobe forum link I posted.
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Just guessing here, but perhaps early binding can force Excel to use the 32-bit version of Photoshop. If so, you need to get rid of the CreateObject step that is causing the error and replace it with the corresponding statements for early binding.
See here for a discussion of early binding versus late binding http://www.dicks-clicks.com/excel/olBinding.htm
0
 

Author Comment

by:Garyfriedman2005
Comment Utility
Well, as I mentioned above, the cs5 libraries aren't checked in the references box, so I would think it *wouldn't* find the CS5 photoshop.  

So assuming what you say is true, is there a way I can tell it to search for the CS2 version short of uninstalling CS5?  (Thanks for all of your help thusfar, by the way!!!!)

-Gary
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Gary,
If you check the box for the CS2 reference and do the code like the example for early binding in Dick Kusleika's web page, then you should be able to force the reference to be correctly made to the older version.

Brad
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
there are 32 bit and 64 bit versions of Win 7.  64 bit version has some known problems with 32 bit  hardware drivers, requiring that drivers be updated.
                 http://windows.microsoft.com/en-US/windows7/32-bit-and-64-bit-Windows-frequently-asked-questions
0
 

Author Comment

by:Garyfriedman2005
Comment Utility
@byundt: Early binding was indeed being used for Photoshop 9 objects (code name for CS2), so as far as I can tell everything is being done correctly.

@puppydogbuddy: Read through the link you provided. It turns out that Adobe had installed both the 64-bit AND the 32-bit version of CS5, so that should have been a selectable (bindable) option, but when I try it comes up with an error.

This is all very educational; but all I'm learning is that I've been doing the right things all along! :-(
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Garyfriedman2005
Comment Utility
@puppydogbuddy: It's not clear that this is relevant.  All my hardware is working just fine so the drivers must all be healthy.
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
Au contraire. You are using late binding with Photoshop.

Dim objapp As Object          'Late binding, as done in your code
Set objapp = CreateObject("Photoshop.Application")         'Late binding, as done in your code

Dim objapp As Photoshop.Application          'Early binding. What I am suggesting that you try.
Set objApp = New Photoshop.Application    'Early binding. What I am suggesting that you try.

Open in new window


Brad
0
 

Author Comment

by:Garyfriedman2005
Comment Utility
AHA!!!!  Let me investigate further!  (I have to go run an errand but will get back to this this evening.)  I will mark this as the solution since it's inconceivable to me that it could be anything else. :-)  

Many thanks!!  

-Gary
0
 

Author Closing Comment

by:Garyfriedman2005
Comment Utility
He kept at it until he found the problem.  Love the persistence!!
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
If early binding works with CS2, it is worth trying to see if it works with CS5 if you click the Browse button in the Tools...References dialog in the VBA Editor. You can select the library for 32-bit Photoshop that way.

Brad
0
 

Author Comment

by:Garyfriedman2005
Comment Utility
The early binding code did the trick!  Thank you once again for your insights.  I can now be productive again.

As noted in my original post, I did indeed try to de-select the CS2 libraries and select the CS5 ones, only to get an "Error opening DLL" even before I had a chance to run the code.  Perhaps Adobe doesn't keep both 32 and 64 bit libraries installed if you have both 32 and 64 programs?

-Gary


0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Gary,
I'm glad to hear that early binding solved your problem.

The error opening the DLL sounds like the reference was pointing to a 64-bit DLL. Was there a similar one that was 32-bit?

The reason I keep picking on this issue is that you ought to be able to specify the correct references using early binding. With late binding, Windows picks it for you--and gets it wrong by choosing the 64-bit DLL. Bear in mind this is speculation on my part, but it does seem plausible.

For future reference, the right place to post Excel VBA questions is in the Excel Zone. The Experts who frequent that Zone are all pretty good with VBA, and more importantly they know the object model. A secondary place to have posted this particular question would have been one of the graphics zones such as http://www.experts-exchange.com/Software/Photos_Graphics/Images_and_Photos/Adobe_Photoshop/

Brad
0
 

Author Comment

by:Garyfriedman2005
Comment Utility
Hi, Brad.

Here are the [the relevant] choices I see when I look at the possible libraries to bind to:

=========

Adobe Photoshop 9.0 Object library  (CS2)
Adobe Photoshop 8.0 Type Library (CS)

(the above are currently checked.)

Adobe Photoshop CS5 Object Library
Adobe Photoshop CS5 Type Library

==========

And that's it!  Deselecting the first two and hitting "OK" produces no error (as long as I don't try to run the code :-) ).  Then selecting the second two and hitting "OK" produces the "Error in Loading DLL" even before I try to run anything.

Clicking on the CS5 Object Library link shows me that it is indeed referring to the 64-bit version, so you were spot-on about that.  But I don't see a 32-bit version to choose anywhere else in the list.

-Gary
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Gary,
If you click the Browse button in that dialog, are you able to locate different libraries? In other words, don't check any of the choices originally displayed--but rather go looking for two new references to an Object library and a Type library.

If you highlight (but don't check the box) a reference, the path and file name should display in the References dialog. This tells you what and where to look. You just need to adapt the path to one that applies to the 32-bit version of CS5 that you installed.

Brad
0
 

Author Comment

by:Garyfriedman2005
Comment Utility
Yes, I actually tried that.  The CS5 libraries that are there are located at c:\program files\adobe\adobe photoshop CS5 (64-bit)\plug-in (after that I can't see the rest of the path, nor is the window expandable to allow me to do so, nor can I select the path and scroll).

Of course there's  no c:\program files\adobe\adobe photoshop CS5 (either 'plain' or '32-bit'), nor is there a version in c:\program files (x86)\*.  It seems Adobe only installed objects for CS5 64-bit.

Hey, wait!  I manualy went to the plug-ins directory and searched in every subdirectory for .dll files and found none!  I wonder if this could be the problem???  (Then why did they appear in the References window to begin with?)

Suddenly totally confused.  -GF

Is this something I can download off of Adobe
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
Gary,
I have a colleague who has Excel 2003, CS5 32-bit & CS-5 64-bit all running under Windows XP-64. So I wrote two test subs, one with early binding and the other with late  binding. I was hoping to prove that one worked and not the other. If so, I could have told you the exact name and location of the Type and Object library files. Unfortunately both of them worked.

The problem you experienced has definitely been reported in other web pages. No solution was ever offered. Lucky for you, the early binding pointing to CS2 does what you need.

Brad
0
 

Author Comment

by:Garyfriedman2005
Comment Utility
Interesting that the problem hasn't been addressed by Adobe thusfar.  Anyway, yes, I am luck - I have an old program that lets me do what I want, and there are people like you who don't mind spending time helping me to troubleshoot.  

Again, thanks so much for your bandwidth!

-Gary
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now