HELP PLEASE !!! on LoadPicture Issue !!!

[tmktech]

Hi Tippers!

I'm desperate for a solution on a problem that's plaguing my customers. I've got an EXCEL VBA 2K & XP app that dynamically modifies both values and an image object) & prints a worksheet multiple times.

The image object's picture is changed with the .LoadPicture method. The problem is that apparently the LoadPicture spawns an asynchronous task to change the object's bitmap, and it is not always in synch with the VBA macro!! It randomly get images out of synch with the values on the sheet.

In thread707-585520, vfsb turned me on to the DoEvents, which I THOUGHT took care of the problem. (This was to fix the fact that the LoadPicture would not complete UNTIL the VBA code finished.)

I've since found out from a customer on a slow LAN that it isn't always enough. I've even inserted about 6 DoEvents throughout the code along the way to give it every chance.

HELP!!! Is there anyway to test for / force the completion of the bitmap change (LoadPicture) before continuing in my code?

This is one will be a lifesaver for me and is begging for a star!

THANKS!

TMKTECH

 

[VBAjedi]

tmktech,

Without some more detail on the big picture of what you're trying to accomplish in your code with the pictures, I don't know if this will help. But here's an "outside-the-box" idea. . .

If the pictures are so slow in LOADING, why not have TWO subs. The first would load all the pictures you want to work with (but keep them invisible, or even on a hidden sheet). Don't put in any delays at all, just let it run as fast as it can. When it finishes, the second sub would fire, processing the images as desired and (if necessary) deleting the invisible ones.

There's almost always a way. . . (even if this ain't it!)



VBAjedi

 

[tmktech]

Thanks for the suggestion, VBAjedi, but it's not practical in my application. Here's some more info to hopefully make it clearer.

This is essentially a publishing application that is modifying "template" worksheets - including a logo (the image)- and printing (and other outputs) each iteration of them.

Here's some very brief psuedocode of the process.

Do while
Image1.LoadPicture(image_path_name)
DoEvents
DoEvents '1st doevent didn't cut it

- misc. other code -
DoEvents
- misc. other code -
DoEvents
- misc. other code -
DoEvents

Select worksheet array

If printing selected then
Selection.PrintOut ...
endif

if pdf selected then
SelectionPrintOut, activeprinter=pdfwriter,
prttofile=true, filename = ...
endif

if excel selected
--- copy & paste code ---
Saveas ...
endif

Loop

-- close up ---



VBAJedi, I've seen code that loops as follows, but I'm not sure how it works (no condition for the While command).

Do While
DoEvents
Sleep 1 'Kernel32 "Sleep" declared elsewhere)
Loop


ALSO, I'm not surer if it's the printing, file creation, or the bitmap update (or a combination) that causes the synching issue. WHERE (if anywhere) do I put the DoEvents, and how many?

 

[VBAjedi]

Maybe I just don't get it, but I'm still not seeing a reason why you can't put the pictures in a more accessible place (i.e. on the local machine instead of across a LAN) by loading them onto a hidden sheet, or a hidden form, or whatever. . .

I suspect you are correct and it is the LoadPicture method that's giving you trouble. If preloading the pictures won't work for some reason, and DoEvents is'nt cutting it either, then I'm out of ideas.

Zathras? TonyJ? Anyone else wanna go at this?



VBAjedi

 

[tmktech]

VBAjedi:

Thanks for the feedback. I'll take any that I can get.

How about this angle of attack...

Is there a way to know when the bitmap has changed on the Image? An API call of otherwise that I can put within the
loop above?

Also, DOES the loop above (Do While without an explicit condition) work? If so, can you (or anyone else) help me to understand what condition is met that's ending the loop?