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
 

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,081,659
Messages
5,360,313
Members
400,580
Latest member
Drexl88

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top