ActiveSheet.OLEObjects.Add - method inserts icon instead of image.

[bdats]

All,

I'm currently running Excel 2000 (SR-1) on a Win98SE box, and my users
are running Excel 2002 on Win2000 machines in a networked setting.

I have an application that functions as a report database of sorts. It
allows a user to select a report/study document (.doc, .txt, .bmp,
..jpg, etc), add descriptive information regarding the report/study and
append that information along with filespec to the data table.

It then creates a small image of the document and places this image
into a comment box in a certain column of the new record.

The idea is to aid the user in locating a specific report/study by
having a small image of it pop up when they hover over the description
column with the cursor.

Up until recently this has all worked nicely in both environments. But
a couple of weeks ago it started placing an "icon" image into the
comment box instead of the image itself when the document is a .jpg
and adding an unwanted label when the document is a .bmp. This is
happening on my computer as well as theirs. Also, the ".jpg" problem
is intermittent.

In creating the image I do the following:
1. Select an "out of the way" cell.
2. Clear the clipboard.
3. Create and paste an image of the report file at the selected cell
using:
ActiveSheet.OLEObjects.Add(Filename:=FileSpec, Link:=False,
DisplayAsIcon:=False).Select

For a .jpg file the image created at this point is an icon image. For
a .bmp, the image is an actual view of the file but with a label at
the bottom.

I'm at a loss to understand what went wrong. I've included the portion
of my code involved below. Any help with this would be greatly
appreciated. I'll post any additional info at your request - just not
sure what else may be pertinent yet.

Thanks again for your help.



'**************************************************************************************************************************************

Sub PreviewPic(strReportName As String, strReportFileSpec As String,
strPreviewSpecCell As String, strPreviewCell As String)
Application.ScreenUpdating = False

Excel.Application.Sheets("ReportData").Range("DA5").Select
Call ClearClipboard
Call CreateAndPlacePreviewPicIntoClipboard(strReportFileSpec)

 

[Ron de Bruin]

Hi

I not try your code but you can use this to display a picture in a comment attached to a cell
http://www.contextures.com/xlcomments02.html#Picture



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

All,

I'm currently running Excel 2000 (SR-1) on a Win98SE box, and my users
are running Excel 2002 on Win2000 machines in a networked setting.

I have an application that functions as a report database of sorts. It
allows a user to select a report/study document (.doc, .txt, .bmp,
.jpg, etc), add descriptive information regarding the report/study and
append that information along with filespec to the data table.

It then creates a small image of the document and places this image
into a comment box in a certain column of the new record.

The idea is to aid the user in locating a specific report/study by
having a small image of it pop up when they hover over the description
column with the cursor.

Up until recently this has all worked nicely in both environments. But
a couple of weeks ago it started placing an "icon" image into the
comment box instead of the image itself when the document is a .jpg
and adding an unwanted label when the document is a .bmp. This is
happening on my computer as well as theirs. Also, the ".jpg" problem
is intermittent.

In creating the image I do the following:
1. Select an "out of the way" cell.
2. Clear the clipboard.
3. Create and paste an image of the report file at the selected cell
using:
ActiveSheet.OLEObjects.Add(Filename:=FileSpec, Link:=False,
DisplayAsIcon:=False).Select

For a .jpg file the image created at this point is an icon image. For
a .bmp, the image is an actual view of the file but with a label at
the bottom.

I'm at a loss to understand what went wrong. I've included the portion
of my code involved below. Any help with this would be greatly
appreciated. I'll post any additional info at your request - just not
sure what else may be pertinent yet.

Thanks again for your help.



'*******************************************************************************************************************************
*******

Sub PreviewPic(strReportName As String, strReportFileSpec As String,
strPreviewSpecCell As String, strPreviewCell As String)
Application.ScreenUpdating = False

Excel.Application.Sheets("ReportData").Range("DA5").Select
Call ClearClipboard
Call CreateAndPlacePreviewPicIntoClipboard(strReportFileSpec)

.
.
.


End Sub

'*******************************************************************************************************************************
*******

Public Sub ClearClipboard()
Dim MyDataObj As New DataObject
MyDataObj.SetText ""
MyDataObj.PutInClipboard
End Sub

'*******************************************************************************************************************************
*******

Sub CreateAndPlacePreviewPicIntoClipboard(strFileSpec)
On Error GoTo Oops
ActiveSheet.OLEObjects.Add(Filename:=FileSpec, Link:=False,
DisplayAsIcon:=False).Select
Selection.Copy
Selection.Delete
Exit Sub
Oops:
OkyDoky = False
End Sub

'*******************************************************************************************************************************
*******

 

[bdats]

Hi Ron,

Thanks for taking the time to answer my post. Hope all is well with
you and yours.

Contextures is definitely a tremendous website and resource. I have
visited it several times and have learned a great deal from it. Thank
you for sharing it with the group. It does seem to be the only site
that comes to mind that has anything to say about placing images into
comment boxes.

I apologize for not making my question clearer. The problem is not in
being able to paste an image into a comment box, that is working well
and under program control.

The problem is that even though I am using "DisplayAsIcon:=False", the
"ActiveSheet.OLEObjects.Add(Filename:=FileSpec, Link:=False,
DisplayAsIcon:=False).Select" statement is inserting images of
document icons instead of images of the actual .jpg, .wmf and .html
documents.

For .bmp documents, it is inserting an image of the document - so far
so good - but then it is adding an unwelcome label at the bottom.

Does anyone know what may be causing this to occur? Perhaps someone
might know what to watch out for in using this method. Things that it
may be sensitive to or dependent on. I chose to use this statement
because it enabled the users to select several different document
types without my having to handle them all seperately.

It worked correctly for several months before this began so I must
have begun doing something elsewhere in the code that is indirectly
affecting this statement. But I'm at a loss to pin it down.

Again, thanks a million for your help.

 

[Dave Peterson]

If you run your code against a new workbook with not much running in the
background, does it work ok?

I'm not sure if I'm misremembering, but it seems to me that when resources are
very low, MSOffice (word and excel) start showing icons.

I still use win98 and when I'm messing around and have lots of stuff open, I can
get this.

If it works on a new workbook, maybe that's the cause.

===
And there's an option under Tools|Options|view|Object section
that says "show placeholders"

(although I'm at a loss how some would appear ok and some would be icons.)

 

[Ron de Bruin]

Hi

I am curious what Dave's suggestion (new workbook) will do
on your machine

 

[bdats]

Dave,

Thanks for the help. I tried placing the code in an otherwise empty
workbook and it still inserted icons.

Even so, your suggestion is the best premise I have to go on, and I'm
not convinced yet that the level of system resources aren't the cause
of this problem.

As the user's have used the app, the file size has grown accordingly
as has the amount of processing being done by my other code. Also the
amount of features requested has grown along with the size and
complexity of the code itself.

This growth mirrors the appearance of this problem and I'm looking
closely at it's effect on system resources outside of file size.

In the interest of getting this thing back up quickly, I modified the
code so that .jpg, .bmp, .gif and .wmf files are read directly into
the Excel.Application.Selection.ShapeRange.Fill.UserPicture property,
bypassing the OLEObjects.Add step entirely. I still need that step to
bring .doc (which are the main files referenced by the users) and .rtf
files in, but they don't seem to cause the "icon" problem.

Ron,

I'm wondering if you weren't trying to tell me that in your prior
response. If so, you were right on the money Thanks for the tip!


Both of you have a safe and happy holiday.

 

[bdats]

Ron,

Per Dave's suggestion, I tried placing the code in as empty a workbook
as I could setup and it still inserted icons.

In spite of that I tend to believe that the level of system resources
is the cause of the problem. Please see my response to Dave's posting.

What I ended up doing is changing the programming so that .jpg, .bmp,
..gif and .wmf files are read directly into the
Excel.Application.Selection.ShapeRange.Fill.UserPicture property.

This bypasses the OLEObjects.Add step and the problem entirely. I
still use it to bring .doc (most of the user's files are .doc files)
and .rtf files into the comment boxes. However, .doc and .rtf files
don't seem to suffer from the "icon" problem.

I've been wondering if you weren't trying to tell me that the graphic
files could/should be referenced directly in your prior response. Good
catch and thanks for the tip!

I wish you and Dave both a safe and happy holiday.