Shapes you can right click on one of those objects and choose to save that as a picture sadly however when you try to record that in the macro recorder or even.
Access the option in excel vba the option simply isn't available so the work around for this is a fairly messy work around is to create a chart object on the page and then copy the.
Picture into the chart object and then use the export method of the chart so we'll take a quick look at how that works in this video so i've started by creating my shapes.
And then grouping them together and i've also given my groups sensible names so with the group of objects selected you can rename them by either typing a name into the name box in the top left hand.
My chart group the the group name there to the clipboard and then we'll start by writing some code that will get a reference to that group of shapes so in the visual basic editor i've got a.
New module and i've written my subroutine save shape as picture i'm going to start by declaring a variable called grp short for group as shape now i want to get a reference to that.
Shape object so i'm going to say set grp equals and i'm just going to reference the sheet one object using its code name of course there are various other ways to do this.
And then i'm going to refer to the shapes collection on there and then in some round brackets and double quotes paste in my chart group and then close the double quotes and close around.
Brackets once i've done that i can copy a picture of that group of shapes just by saying grp dot copy picture this is where ideally we'd have an export or a save as.
Posts Related:
- How to Design Student Classroom Diary/Planner in MS Word DIY Tutorial
- Webinar Triggers & Functions
- Where do Business Leaders get Stuck when Adopting Power BI Talk Power BI LIVE Q&A (Feb 5, 2021)
Picture method but sadly none of those
Options are available so we're just going to copy that object as a picture next i want to create a new chart object with the same dimensions as the group of.Shapes i've just copied so to help with that let's declare a new variable i'll call mine co as chart object and then i'll create that chart on the same worksheet as the group of shapes so.
We'll say set co equals sheet1 dot chart objects dot add now it would be nice to get some help from the intellisense and the tooltips at this point but sadly it breaks down.
So to get a bit of extra information you could try clicking on the chart objects keyword hit the f1 key on your keyboard to fire up the context sensitive help system taking you straight off to the.
Chart objects method help page from here i'm going to follow the link to the chart objects object and then from here on the left hand side in the methods list i can find the add.
Method and finally see the four parameters of the add method of a chart objects object so i'm going to set the left top width and height parameters using the same.
Properties as a group of shapes so back into the visual basic editor i'm going to refer to group dot left group dot top.
Group dot width and finally group dot height then having done that let's just try running the subroutine at this point to see the end results so if we switch back.
To the excel window we should see a new shape sitting on top of the existing group of chart objects you can see it's appeared here on the selection pane on the right hand side if.
I click on that to select it
Obviously at the end of the procedure i'd want that chart to be deleted i'm just going to manually delete it for now so it's gone.And then i'm going to head back to the visual basic editor and just to make sure that at the end that chart object gets removed i'm going to say co.delete and then we'll do the the useful stuff.
In between those lines so one last go i'm going to just move this out of the way briefly use the fh key to step through so we can see the chart object gets created and then it gets deleted at.
The end and that's the end of the procedure next i just want to paste the copied picture into the chart of the chart object we've created and the instruction.
To do that is pretty straightforward we can just say co chart dot paste now there's a bit of a potential gotcha here that affects me at least on my own machine.
If i just stepped through this procedure nice and slowly so we can see what's going on we can use the f8 key to do that we should be able to see that once we've.
Copied or created the new chart object pasting the picture of the chart makes it appear and then we can delete it of course we'd save it as an image first watch what happens though if i just.
Comment out the delete line and i just run this one as fast as it will go it definitely creates the chart object but the picture doesn't appear to get pasted.
And i found this works more successfully i'm just going to manually delete that extra charge let's get rid of that found this works a little more successfully if we select the chart object first so i'm.
Going to say co dot select and then again run this one as fast as it'll go and this time the object does get the chart or the other picture pasted into the chart.
Let's just get rid of that one again by manually deleting it and then oops i think i've deleted the wrong thing let me just try that again let's select and delete there we go and.
Then finally we can get back to the code and deal with the saving as a picture so finally to save the chart as a picture we can apply the export method to it so we can say co.chart dot export.
And we'll see there's a single compulsory parameter which is the file name and an optional parameter called filter name which allows you to specify the graphic filter this really does.
Appear to be optional we can set the uh the graphic type just by applying the correct file name extension but just to demonstrate if i were to save this file in the same folder as the workbook that.
This code is stored in i'm going to say export this workbook dot path and then concatenate to the end of that a backslash and let's call this one chartpic.
We could save it as a png first for example and if you were going to specify the filter name you can just enter the png abbreviation there as well portable.
Network graphics so it doesn't appear to be necessary you can save with the the file name extension and it'll still save as a png file just to prove that.
That's created there it is have a quick look there's the final image and we can of course change that to any of the other standard file types so let's go for jpg.
And again i haven't actually tried mixing matching the filter names with the other file name extensions and maybe just to prove that you don't need that part let's take away the filter name run.
It again and we've got a jpg file instead looks pretty similar to me and then we could use a bmp if you prefer uh i don't know why you'd use uh bitmaps.
But there we go um and then what else can we go gif or jif how do you pronounce it i genuinely do not care um gif or gif whatever your personal.
Preference there we go so they'll work pretty nicely so there we go there's the final code to save a group of shapes as a picture i appreciate it it's a bit of a faff um.
But at least it works it seems fairly reliable and it applies not just to charts which was what the original question was all about but also to generic collections of standard auto.
Shapes so i've got a my shape group object here if i just alter the name of the uh the the shape i was referring to in the.
First part and then just call this one shape pick for example when we export it let's go back to a png and then run the subroutine again we.
Should see a new um shape pic file has appeared so you can do it for any combination of shapes you can group together on a worksheet hopefully that answers the original.
