hi expert..
i'm looking vba code to export shape/image/object as picture (as .png format)
this step macro can do it:
1. first time, select your shape/image/object, then run macro
2. browse folder location to save...
3. save as .png format
4. the macro code working in active sheet (any name sheet).
5. the code can made as excel add-ins , please don't make/save in .xlsm format
here attachment excel file
thank in advance
.susanto
VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
-
- 3StarLounger
- Posts: 240
- Joined: 17 Feb 2022, 05:16
VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
Here is a macro:
Code: Select all
Sub ExportAsPNG()
Dim wsh As Worksheet
Dim shp As Shape
Dim fil As Variant
Dim cho As ChartObject
On Error Resume Next
Set shp = Selection.ShapeRange(1)
On Error GoTo 0
If shp Is Nothing Then
MsgBox "Please select a shape/picture, then try again!", vbExclamation
Exit Sub
End If
fil = Application.GetSaveAsFilename(InitialFileName:="*.png", FileFilter:="PNG files (*.png), *.png")
If fil = False Then
MsgBox "You didn't specify a filename!", vbExclamation
Exit Sub
End If
Set wsh = ActiveSheet
Set cho = wsh.ChartObjects.Add(Left:=shp.Left, Top:=shp.Top, Width:=shp.Width, Height:=shp.Height)
shp.Copy
cho.Select
ActiveChart.Paste
ActiveChart.Export Filename:=fil, FilterName:="PNG"
cho.Delete
End Sub
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 240
- Joined: 17 Feb 2022, 05:16
Re: VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
hi Hans,
it worked!!! Thank you very much.
it worked!!! Thank you very much.
-
- NewLounger
- Posts: 6
- Joined: 21 Apr 2023, 08:17
Re: VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
Hi,
I tried this and it only seems to work if i put the macro on 1 object.
I would love to select lets say 60 objects and export them all at once, is this possible?
The ideal thing would be if it takes the name from the cel next to it.
This is how my file looks:
I tried this and it only seems to work if i put the macro on 1 object.
I would love to select lets say 60 objects and export them all at once, is this possible?
The ideal thing would be if it takes the name from the cel next to it.
This is how my file looks:
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
Welcome/welkom to Eileen's Lounge!
Try this version:
Try this version:
Code: Select all
Sub ExportAllAsPNG()
Dim fld As String
Dim wsh As Worksheet
Dim shp As Shape
Dim rng As Range
Dim fil As Variant
Dim cho As ChartObject
' Folder path - I used the folder of the workbook
fld = ActiveWorkbook.Path
If Right(fld, 1) <> Application.PathSeparator Then
fld = fld & Application.PathSeparator
End If
Set wsh = ActiveSheet
For Each shp In wsh.Shapes
Set rng = shp.TopLeftCell
If rng.Column = 2 Then ' column B
fil = fld & rng.Offset(0, -1).Value & ".png"
Set cho = wsh.ChartObjects.Add(Left:=shp.Left, Top:=shp.Top, Width:=shp.Width, Height:=shp.Height)
shp.Copy
cho.Select
ActiveChart.Paste
ActiveChart.Export Filename:=fil, FilterName:="PNG"
cho.Delete
End If
Next shp
End Sub
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 585
- Joined: 14 Nov 2012, 16:06
Re: VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
Adapt the export directory
If you select shapes beforehand:
instead of
use
Code: Select all
Sub M_snb()
c00 = "G:\OF\"
With Sheet1.ChartObjects.Add(1, 1, 100, 100).Chart
For Each it In Sheet1.Shapes
If it.TopLeftCell.Address <> "$A$1" Then
it.CopyPicture
.Paste
.Export c00 & it.TopLeftCell.Offset(, -1) & ".png"
.Shapes(1).Delete
End If
Next
.Parent.Delete
End With
End Sub
instead of
Code: Select all
For Each it In Sheet1.Shapes
Code: Select all
For Each it In Selection.Shapes
-
- NewLounger
- Posts: 6
- Joined: 21 Apr 2023, 08:17
Re: VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
Thanks for all the help guys,
The solution from Hans already did the trick for me.
The solution from Hans already did the trick for me.
-
- NewLounger
- Posts: 3
- Joined: 20 Aug 2023, 17:03
Re: VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
Hi Hans
I have tried your code to save all pictures as png, but I get an error when about 13 pictures has been saved.
But if I put in a wait statement (1 sec.) inside the loop then I runs without problems, do you have any idea why?
I have tried your code to save all pictures as png, but I get an error when about 13 pictures has been saved.
But if I put in a wait statement (1 sec.) inside the loop then I runs without problems, do you have any idea why?
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
Welcome to Eileen's Lounge!
That's probably because there wasn't enough time between the commands without the Wait statement.
One or two lines
DoEvents
inside the loop might also work.
That's probably because there wasn't enough time between the commands without the Wait statement.
One or two lines
DoEvents
inside the loop might also work.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 3
- Joined: 20 Aug 2023, 17:03
Re: VBA : Export Shape/Image/Object As Picture (.PNG Fomat)
It worked perfectly, thanks a lot
That's probably because there wasn't enough time between the commands without the Wait statement.
One or two lines
DoEvents
inside the loop might also work.