Open a png file to put as a comment background for a pop up image in Microsoft Excel

Question

I am trying to put comment background based on a certain field in the excel sheet. The field hold the filename of the .png file.

This is in Mac.

Here I have previously created a macro to add a picture in the comment:

Sub InsertPic()
'
' InsertPic Macro
'
' Keyboard Shortcut: Option+Cmd+p
   strFileToOpen = Application.GetOpenFilename
   MsgBox strFileToOpen
   ActiveCell.AddComment
   With ActiveCell.Comment.Shape
       .ScaleWidth 5, msoFalse, msoScaleFromTopLeft
       .ScaleHeight 5, msoFalse, msoScaleFromTopLeft
       .Fill.UserPicture strFileToOpen
   End With
End Sub

Here it opens a dialogue box with GetOpenFilename but now I want to get the filenames in a loop. If I put one FileName in a string

strFileToOpen = "/path/FileName.png"

It gives out of memory! Any Clues?

Sgdva · Accepted Answer · 2016-06-08T03:11:31.823

0

UPDATE:
You need to add the reference Microsoft Scripting Runtime in order to use the variable type "Scripting.FileSystemObject".

This works for me

Sub InsertPic()
'
' InsertPic Macro
'
' Keyboard Shortcut: Option+Cmd+p
Dim FileExists As Variant
Dim FileSystemLibrary As New Scripting.FileSystemObject
    On Error GoTo Err01InsertPic
    Set FileExists = FileSystemLibrary.GetFile(ActiveCell.Value)
   ActiveCell.AddComment
   With ActiveCell.Comment.Shape
       .ScaleWidth 5, msoFalse, msoScaleFromTopLeft
       .ScaleHeight 5, msoFalse, msoScaleFromTopLeft
       .Fill.UserPicture FileExists
   End With
   If 1 = 2 Then ' 99. If error
Err01InsertPic:
   MsgBox ("Picture " & ActiveCell.Value & " doesn't exists!"), vbCritical
   End If ' 99. If error
End Sub

edited Jun 08 '16 at 03:11

answered Jun 07 '16 at 21:39

Sgdva

2,800
3
17
28

Hi Sgdva, Thanks for your response. I have all the file Names in the xlsx so I do not need to loop through the filenames. I need to get a `FileItem` from that path. If I put `.Fill.UserPicture` with a String it errors out Saying "Out of Memory". However the code where I use `GetOpenFilename` it does not fail although `strFileToOpen` is a string type. The code for me should be simple where I just loop through all the records in the xlsx and add the corresponding comment. – user 923227 Jun 07 '16 at 22:33
When I am using `CreateObject("Scripting.FileSystemObject")` I am getting the error `ActiveX component can't create object` – user 923227 Jun 07 '16 at 22:43
Odd, late binding precisely prevents that,check [this topic](http://stackoverflow.com/questions/5457000/error-activex-component-cant-create-object-scripting-filesystemobject) to fix it -if reference doesn't work-. You are getting "Out of memory" because the archive has to be declared as file prior, updated my response. – Sgdva Jun 08 '16 at 03:08
Okay my bad. I am on Mac, there is no registry. So I cannot update Scripting.FileSystemObject under HKCR. I looked at some other posts and they say to use Dir. But Dir just returns the FileName right? – user 923227 Jun 08 '16 at 21:19
You'd need to check how to declare a file type variable to VBA from MAC then change the Dim FileSystemLibrary As New Scripting.FileSystemObject accordingly, I don't have OSX so can't guide you any further regarding that. – Sgdva Jun 08 '16 at 21:55

Open a png file to put as a comment background for a pop up image in Microsoft Excel

1 Answers1