Save selection as image, export to network location

[DKcrm]

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

 

[patel45]

to save as image

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

 

[DKcrm]

to save as image

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]

a link to a sample file will be helpful

 

[DKcrm]

a link to a sample file will be helpful

Of course...
http://dl.dropbox.com/u/98156961/ExcelWeekplan_public.xlsx

The file is a sample file - The range can vary, which is why it needs to be based on selection rather than pre-defined range

 

[patel45]

you can try this

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

 

[DKcrm]

Seems to do the exporting trick - Thanks

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