Inserting Picture to Cell Location Mac 2011

[Caleeco]

Hello,

I'm trying to write a piece of code that will insert a local .png picture into a cell location in Excel. However, the twist is, this is for Mac 2011 Excel!

So this code, will work on Windows.. but not Mac. It falls over at the .Insert method.

<code class="prettyprint prettyprinted" style="box-sizing: border-box; padding: 0px; font-size: inherit; font-family: Menlo, Monaco, Consolas, 'Courier New', monospace; color: inherit; border-radius: 0px; white-space: pre-wrap; background-color: transparent;">
</code>SubAddSignature()

Application.ScreenUpdating = False

Dim SigPath As String
Dim tSigFile As String
Dim tSig As String

With Worksheets("Macro Variables") 'ReadInputVariables(FilePath&FileName)
SigPath=.Range("D7").Value
tSigFile =.Range("D10").Value
EndWith

tSig =SigPath& tSigFile 'Concatenate File Path & File Names

Worksheets("Timesheet").Range("G27:H27").Select 'Define range to place img
ActiveSheet.Pictures.Insert(tSig)

Application.ScreenUpdating=True
EndSub<code class="prettyprint prettyprinted" style="box-sizing: border-box; padding: 0px; font-size: inherit; font-family: Menlo, Monaco, Consolas, 'Courier New', monospace; color: inherit; border-radius: 0px; white-space: pre-wrap; background-color: transparent;"></code>

From what I've read, Macs work slightly different. This syntax is wha ive seen online.

Dim pasteCell as range
Dim theShape as Shape

Worksheets("Timesheet").Range("G27:H27").Select 'Define range to place img
set pasteCell =Worksheets("Timesheet").Range("G27")
pasteCell.Select

Set theShape =Worksheets("Timesheet").Shapes.AddPicture("FILEPATH HERE", pasteCell.Left, pasteCell.Top,200,200)

Any ideas how to get this working? Its hard for me to test as i dont have a mac

Many Thanks
Caleeco

 

[yky]

Sheets.Pictures.Insert and Sheets.Shapes.AddPicture are two different ways of inserting pictures. Both work on Windows. Not sure about Mac. Maybe you can first make the AddPicture method work on Windows and see if the same code can be ported to Mac.

 

[RoryA]

What path are you passing, and what exactly does "falls over" mean?

 

[yky]

The following is the actual code I use in one of my VBA program. It first inserts a picture using the Insert method. Once the picture is inserted, I get the dimension of the picture and calculate the factor needed to make the picture fit the width of Column A. Then, I do an AddPicture to add the picture and adjust cell height of Column A so the picture fits in the cell.

The path is the exact file path and the name of the picture file. It looks something like "C:\my photo\my picture.jpg". Mine looks horrible because file names are stored in Column B. The program needs to extract part of the path, the group code, from the file name. Your case might be simpler. You might be able to just use the absolute path in your code.

I would first test the AddPicture method on a PC using a test picture. A one-line code would suffice. It it works, then it's time to see if the code can be ported to Mac.

If Len(Dir(Path_Prefix & Group_Code & "\" & Replace(cell.Value, "/", "-") & ".jpg")) <> 0 Then
[COLOR=#0000ff]Set p = Workbooks(ActiveSheet.Parent.Name).Sheets(Sheet_to_Insert_Picture).Pictures.Insert(Path_Prefix & Group_Code & "\" & _[/COLOR]
[COLOR=#0000ff]Replace(cell.Value, "/", "-") & ".JPG")[/COLOR]

p.Left = ActiveSheet.Cells(cell.Row, Picture_Column).Left
p.Top = ActiveSheet.Cells(cell.Row, Picture_Column).Top
cell_width = ActiveSheet.Cells(cell.Row, Picture_Column).Width
cell_height = ActiveSheet.Cells(cell.Row, Picture_Column).Height

factor = cell_width / p.Width
ActiveSheet.Cells(cell.Row, 1).RowHeight = p.Height * cell_width / p.Width

temp_width = p.Width * factor * 0.9
temp_height = p.Height * factor * 0.9

Workbooks(ActiveSheet.Parent.Name).Sheets(Sheet_to_Insert_Picture).Shapes(p.Name).Delete

[COLOR=#0000ff]Set p = Workbooks(ActiveSheet.Parent.Name).Sheets(ActiveSheet.Name).Shapes.AddPicture(Filename:=Path_Prefix & Group_Code & "\" & _[/COLOR]
[COLOR=#0000ff]Replace(cell.Value, "/", "-") & ".jpg", LinkToFile:=False, SaveWithDocument:=True, Left:=ActiveSheet.Cells(cell.Row, Picture_Column).Left + _[/COLOR]
[COLOR=#0000ff](ActiveSheet.Cells(cell.Row, Picture_Column).Width - temp_width) / 2, _[/COLOR]
[COLOR=#0000ff]Top:=ActiveSheet.Cells(cell.Row, Picture_Column).Top + (ActiveSheet.Cells(cell.Row, Picture_Column).Height - temp_height) / 2, Width:=temp_width, Height:=temp_height)[/COLOR]