I use Excel for basic statistical analysis of manufacturing and field return data as part of daily routine. I also use Excel for department budgeting. During these tasks, I frequently need to add information to my spreadsheets about my information sources (see Wikipedia entry on data provenance). Since much of my information comes to me in the form of email or web pages, a quick and easy way to document this information is to take a screenshot of the information and insert the information into an Excel comment.
For years I have included images in Excel comments using the manual procedure described here. This approach works well but is slow. I finally decided that there has to be a better way and I wrote a Visual Basic for Applications (VBA) macro that handles all the ugly details. Here is a link to this spreadsheet. The spreadsheet does use a VBA macro. If you are concerned about security, just look in the macro and see what it does.
To see a sample image (Saturn), just mouse over the cell with the little red triangle in the upper right corner.
A little discussion on how to use the macro is in order:
- Put an image into your clipboard (I use PicPick)
- Select the cell where you want to put the image
- Click the button labeled "Press"
- Your selected cell will now have little red triangle in the upper right corner. You will see your image when you mouse over this cell.
- I used a button for this example, but in my work I assign both a Quick Access toolbar icon and a keyboard shortcut to this macro.
Many thanks to the folks at these two web sites for posting code and clues that helped me put this macro together.
Im looking for this a long time now, It works but I keep getting an error afterwards..."The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."
Hi Sam,
Thanks for catching this. I will update the code this weekend. Again, appreciate the help.
mark
Thanks Mark, I was at the point of giving up.
So what I really need is a macro that put or paste the image which is residing in the clipboard into the comment of the active cell, or a macro that cuts the pre-pasted image which is lying temporarily on a cell, into the comment of any desired cell
Could you tell me what version of Excel you have? It is working on all my versions. I am currently running Office 365 Version 1609 at work and home, however the routine ran with 2007 (32 bit) and 2010 (32 bit). What version are you running? Look under File/Account.
mark
Hi Mark
You might be absolutely right on the compatibility. And that will be great news for me, as I need it for my Work computer, not my MacBook Air below. Sorry I should have told you upfront that I encountered this problem at home with my Mac, not at my work which has Office 10. I will let you know by this coming week when I tried it out at Work.
My version of Excel for my MacBook Air (Mid 2012) ,
macOS Sierra is .....
Microsoft Excel for Mac
version 15.32 (170309)
Product ID: xxxxxxxxxxxxxxxx
License: Office 365 Subscription
2017 Microsoft
Hi Mark,
Sorry for the lapse, yes it works smooth and flawlessly on my job's pc. Thanks for your masterpiece. i wish it could work same way on my mac too. Hope you fix that someday.
Hi Mark,
I’ve been using your wonderful “ImageToComment” utility for the past year or so and it’s proven to be very useful and quite a time-saver. However, since upgrading to a 64-bit version of Excel, it no longer works. I get the same error message that Sam reported. Did you ever get a chance to update the code to support 64-bit Excel? If so, where can I find it?
I’m running Excel 2016 in Windows 7. Your macro works fine with 32-bit Excel but not 64-bit Excel.
Thanks!
George
Hi George,
I will need to find a 64-bit version to play with. Give me a few days. My work is still on 2010, so no help there.
Mark
Hi Mark,
I was wondering whether you had any luck tracking down a 64-bit version of Excel and updating your utility to support it?
Cheers,
George
Hi George,
I have identified changes that were made in the clipboard API between the 32-bit and 64-bit interfaces. This may be the issue. I will continue to try to get a 64-bit Excel copy – hopefully, when I see my son this weekend.
biegert
I now have a 64-bit copy of Excel and the routine fails. I will be working on it in the next few days. Darn Microsoft can't leave well enough alone.
mark
I still have not tracked down a 64-bit copy of Excel. On the bright side, I am buying a new computer – it is a custom. It will be here in 3 weeks. I will put 64-bit Excel on that unit. I am reluctant to put it on my current unit because I depend on that unit for my income. I don't want to break anything.
biegert
Hi Biegert,
Were you able to check the issue in office 64 bits?.
It is not working on Excel 365 (16.0.12718.20004) 64-bit 🙁
Thanks.
Kind regards,
Andrés
Hi Biegert,
Any news about your great macro running on Excel 64 bits?..
Thanks.
Andres
Hi Andres,
I wasn't sure exactly what changed in the Microsoft system routine being called. I am closer to figuring it out. Kind of a nasty change.
biegert
Thanks Biegert.
I´ll be waiting for your solution.
Kind regards,
Andres
I've been googling "excel vba photo comment" and variations of those keywords for two days now and very luckily found your post. Thank you so much for creating this and posting why you built the tool. I'm also looking for a way to quickly capture data sources or "data provenance" via a screen snippet into a comment. This saves so much time. Thanks!!
FYI - I'm running an x64 system and your code is working fine.
Tried this and still not updated for 64 bit systems. Throws compile error.
I have not found a way to access images on the 64-bit systems. I have a question into an expert on the subject. I want it to work myself.
Does this imbed the image into the file or does it use a link? I need to be able to send this spreadsheet to others and not worry about finding and sending linked files/images.
On 32-bit systems, it embeds the image. On 64-bit systems, there is a pointer issue that I am trying to work through. It has been thorny.
Thanks for the quick response. Good luck finding the solutions. It will be a huge time saver for me and many others when you do get it working.
Thanks for excellent code.
Some pictures are too large, so I needed to zoom or strech to "unified" dimensions.
I just added this code before the block, where you have determined the cell:
'Picture zoom/stretch
ImageRatio = ImageWidth / 640
If ImageWidth 640 Then
ImageWidth = 640
ImageHeight = ImageHeight / ImageRatio
End If
Where 640 is a maximum number of pixels of one side of the picture and sides remain in the same ratio.
Of course definition of ratio must be added.
Dim ImageRatio As Integer 'Image width/height ratio
Hope this helps to everyone.
Regards
Martin
Hello Biegert,
I have found another way to do the image-paste. The code is not mine, I found it somewhere, but unfortunately don't remember where. For sure there is a lot of room for improvements.
If you want, have a look to it, and see how you can improve it.
*****
Option Explicit
Sub PictureExport()
On Error GoTo ER
Dim TempChart As String, Picture2Export As String
Dim PicWidth As Long, PicHeight As Long
Dim ActSheet As String
'RemoveSpaces from sheet name
'Dim Ws As Worksheet
' For Each Ws In Worksheets
' Ws.Name = RTrim(Ws.Name)
' Next Ws
'END RemoveSpaces from sheet name
Selection.ClearComments
ActSheet = ActiveSheet.Name
ActiveSheet.Paste
Selection.Name = "PrintScreen"
Picture2Export = Selection.Name
'Store the picture's height and width in a variable
With Selection
PicHeight = .ShapeRange.Height
PicWidth = .ShapeRange.Width
End With
'Add a temporary chart in sheet1
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=ActSheet
Selection.Border.LineStyle = 0
TempChart = Selection.Name & " " & Split(ActiveChart.Name, " ")(2)
With ActiveSheet
'Change the dimensions of the chart to suit your need
With .Shapes(TempChart)
.Width = PicWidth
.Height = PicHeight
End With
'Copy the picture
.Shapes(Picture2Export).Copy
'Paste the picture in the chart
With ActiveChart
.ChartArea.Select
.Paste
End With
'Finally export the chart
.ChartObjects(1).Chart.Export Filename:=ThisWorkbook.Path & "\tmp.jpg", FilterName:="jpg"
.Shapes(TempChart).Cut
End With
ActiveCell.AddComment
ActiveCell.Comment.Shape.Fill.UserPicture ThisWorkbook.Path & "\tmp.jpg"
ActiveCell.Comment.Shape.Width = PicWidth
ActiveCell.Comment.Shape.Height = PicHeight
ActiveSheet.Shapes("PrintScreen").Delete
Kill ThisWorkbook.Path & "\tmp.jpg"
Application.ScreenUpdating = True
Exit Sub
ER:
MsgBox "An error has occurred. Make sure you have a picture in your clipboard.", vbCritical, "...::: Error :::..."
Application.ScreenUpdating = True
End Sub
******
I've created an add-on so the functionality is always available to be used with a shortcut key.
One of the issues (you will see it in the commented code), is if the sheet name has space, it will fail.
It work on Excel 365 13827. 20004 (Beta Channel).
Please let me know your comments, and if you somehow improved it, please share them also.
Thanks in advance.
Kind regards,
Andres
I will check this out. Looks interesting. Thanks.
mark
Hello There,
Did you have time to look at the code I sent you?.
Any comments or improvements can be done there?.
Thanks.
Andres
Would it be possible to update this script for 64bit machines. It has been a very useful script but now is not functional with newer computers. I get an error that says add Ptrsafe attribute.
Thanks for your help!