doctorhifi
New Member
- Joined
- Aug 13, 2013
- Messages
- 19
I am having trouble copying images between two different workbooks worksheets. I can't seem to get it to paste the image at the original location row and column. Some of the images may be part of a merged cell in the oldwb and may be pasted into a merged row/column in the current worksheet. Any tips?
below is the relevant portion of my code which seems to consistently paste to the correct row but inconsistently pastes to the correct column:
below is the relevant portion of my code which seems to consistently paste to the correct row but inconsistently pastes to the correct column:
Excel Formula:
'COPY IMAGES
‘Set a reference to the Scripting.FileSystemObject
Set fso = New Scripting.FileSystemObject
' Path to store images temporarily
Dim tempImagePath As String
tempImagePath = ThisWorkbook.path & "\TempImages\"
' Check if the TempImages folder exists, if not, create it
If Not fso.FolderExists(tempImagePath) Then
fso.CreateFolder tempImagePath
End If
' Before processing images, clear the previous imgLocations
Dim imgLocations As Object
Set imgLocations = CreateObject("Scripting.Dictionary")
imgLocations.RemoveAll
imgLocations.CompareMode = vbTextCompare ' Set the comparison mode to be case-insensitive
' Loop through each shape in the old worksheet and save images
Dim shp As Object
Dim imgPath As String
Dim destinationCell As Range
On Error Resume Next
For Each shp In oldwb.Worksheets("Cab Pricing Worksheet").Shapes
If shp.Type = 13 Then ' Check if it's a picture
' Store the original cell location of the image
imgLocations.Add shp.name, shp.TopLeftCell.Address
' Copy the shape to the current worksheet
shp.Copy
Set destinationCell = shWrite.Range(shp.TopLeftCell.Address) ' Set destination cell to the original location
destinationCell.PasteSpecial
Application.CutCopyMode = False ' Clear the clipboard
' Save the picture as an image file
imgPath = tempImagePath & "Image" & shp.name & ".png"
destinationCell.CopyPicture Appearance:=xlScreen, Format:=xlPicture
With ThisWorkbook.Worksheets("Cab Pricing Worksheet").Pictures.Paste
.Export imgPath, 2 ' Use 2 for PNG format
.Delete
End With
' Clear the copied shape
destinationCell.Clear
End If
Next shp