Save selection as image, export to network location

DKcrm

New Member
Joined
Mar 2, 2006
Messages
40
Hi,

Been looking through board to find help on this issue, but it didn't seem as if the solutions applied..

With Excel 2010, I want to make a macro that:
- allows users to save selection to an image file (bmp, jpg, gif or png)
- with 'invisible' gridlines (ActiveWindow.DisplayGridlines = False)
- saves to network location (\\server\folder path\....)
- with specific filename relative to the scenario (each scenario will have a specific file name)
- overwrites files in destination folder if filename already exists

So, we have a handfull of Excel files in which charts are generated manually (ie. a Gantt style week plan), and we want to include these in some reporting tools as pictures. Reports are generated in SAP BO, and pictures can be loaded into reports as picture urls.

Standard job when updating these charts in Excel, you save and export your chart as picture file to be loaded into auto-generated reports.

Basically, the code seems pretty simple: De-activate gridlines, Take selection and copy/paste to image file on network location, Overwrite existing if already exists
- I'm willing to give on the scenario part if this turns out to bee too complex. Each scenario could have it's own Macro, and these could be linked to from toolbar macro-by-macro.

Any help, please :)
Thanks,
Christoffer
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

patel45

Well-known Member
Joined
Jul 15, 2012
Messages
1,953
to save as image
Code:
Sub exportgraph()
Dim Graph As Chart, sh As Worksheet, Title As String
Dim fpath As String,  i As Integer
fpath = "C:\test\"
For Each sh In Worksheets
     aname = fpath & sh.Name
     For i = 1 To sh.ChartObjects.Count
            Set Graph = sh.ChartObjects(i).Chart
'          Title = Graph.ChartTitle.Text
           Graph.Export aname & "_" & Title & ".png", "png"
     Next i
 Next
 End Sub
 
Last edited:

DKcrm

New Member
Joined
Mar 2, 2006
Messages
40
to save as image
Code:
Sub exportgraph()
Dim Graph As Chart, sh As Worksheet, Title As String
Dim fpath As String,  i As Integer
fpath = "C:\test\"
For Each sh In Worksheets
     aname = fpath & sh.Name
     For i = 1 To sh.ChartObjects.Count
            Set Graph = sh.ChartObjects(i).Chart
'          Title = Graph.ChartTitle.Text
           Graph.Export aname & "_" & Title & ".png", "png"
     Next i
 Next
 End Sub

Thanks - but how does this work if we're not dealing with charts as in Excel charts, but charts made manually by coloring cells?
 

patel45

Well-known Member
Joined
Jul 15, 2012
Messages
1,953
you can try this
Code:
Sub CopyRangeToJPG()
' save a range from Excel as a picture
Dim rng As Range, Cht As ChartObject
strPath = "D:\DATA\test\"
'Application.ScreenUpdating = False
' Set rng = Range("A1:G7") ' <<<<<<< to be changed 
Set rng = ActiveWindow.Selection
rng.CopyPicture xlScreen, xlPicture
Set Cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width, rng.Height)
Cht.Chart.Paste
Cht.Chart.Export strPath & "myfile.jpg"
Cht.Delete
'Application.ScreenUpdating = True
Set Cht = Nothing
Set rng = Nothing
End Sub
 
Last edited:

DKcrm

New Member
Joined
Mar 2, 2006
Messages
40
Seems to do the exporting trick :) - Thanks

How do I fix the width of the exported image to 800px and locked aspect ratio?
 

Forum statistics

Threads
1,141,492
Messages
5,706,671
Members
421,462
Latest member
Eame20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top