Exporting Excel Range as image (VB.NET)
Solution 1
I just had to hit the MSDN up a little harder to get there. Turns out you have to put the chartobject inside a chart, the code I got working looks like:
xlRange = xlWorkSheet.Range("B2:C6")
xlRange.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlPicture)
Dim oChtobj As Excel.ChartObject = xlWorkSheet.ChartObjects.add(xlRange.Left, xlRange.Top, xlRange.Width, xlRange.Height)
Dim oCht As Excel.Chart
oCht = oChtobj.Chart
oCht.Paste()
oCht.Export(Filename:="C:\saveit.jpg")
oChtobj.Delete()
I was going to delete the question, since it got solved by me so quickly (this ignores the decent bit of time I spent before I posted it here), but when I search for a problem like mine it comes to this page, so maybe this will help someone in the future. If you are looking to copy a range from excel to a jpg for some reason (perhaps attaching it to the body of an outlook email, because that is what I am doing), this should work for you.
Solution 2
And the C# equivalent requires the call to Activate() or COMException will be thrown
Excel.Range xlRange = xlWorkSheet.Range("B2:C6");
range.CopyPicture(Excel.XlPictureAppearance.xlScreen, Excel.XlCopyPictureFormat.xlPicture);
Excel.ChartObject chartObj = myWorksheet.ChartObjects().Add(range.Left, range.Top, range.Width, range.Height);
chartObj.Activate(); // Don't Forget!
Excel.Chart chart = chartObj.Chart;
chart.Paste();
chart.Export(@"C:\image.png");
chartObj.Delete();
asjohnson
I am a 20 something Minnesota based programming enthusiast. My educational background is in mathematics, economcs and statistics, but I have always enjoyed programming and since entering the work world I have realized that you can get a lot of value from programming. I am pretty handy with R and VB.NET is my default "time to program" language. I know some C# and a few other things (reading Learn You A Haskell and doing Codeacademy), but really my programming approach is very stack overflow reliant, so I am never sure exactly how much I know vs. how much I am able to look up online in a reasonable time frame.
Updated on June 04, 2022Comments
-
asjohnson almost 2 years
I have a working excel vba macro that does what I want from here and I am trying to convert it to VB.NET.
The code from VBA:
Sub bah() ''' Set Range you want to export to file Dim rgExp As Range: Set rgExp = Range("B2:C6") ''' Copy range as picture onto Clipboard rgExp.CopyPicture Appearance:=xlScreen, format:=xlBitmap ''' Create an empty chart with exact size of range copied With ActiveSheet.ChartObjects.Add(Left:=rgExp.Left, Top:=rgExp.Top, _ Width:=rgExp.Width, Height:=rgExp.Height) .Name = "ChartVolumeMetricsDevEXPORT" .Activate End With ''' Paste into chart area, export to file, delete chart. ActiveChart.Paste ActiveSheet.ChartObjects("ChartVolumeMetricsDevEXPORT").Chart.Export "C:\Users\ajohnson\Desktop\workdamnit.jpg" ActiveSheet.ChartObjects("ChartVolumeMetricsDevEXPORT").Delete End Sub
What this does is take an excel range and then put it into a chart that is a copy of the range and save it as a JPG.
Here is my most recent attempt at making it VB.NET:
Dim xlApp As New Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim xlRange As Excel.Range xlWorkBook = xlApp.Workbooks.Open("C:\test.xlsx") xlWorkSheet = xlWorkBook.Sheets("Sheet1") xlRange = xlWorkSheet.Range("B2:C6") With xlWorkSheet.ChartObjects.add(xlRange.Left, xlRange.Top, xlRange.Width, xlRange.Height) .name = "Chart1" .activate() End With xlWorkSheet.ChartObjects("Chart1").Paste() xlWorkSheet.ChartObjects("Chart1").chart.export(Filename:="C:\Users\ajohnson\Desktop\saveit.jpg") xlWorkSheet.ChartObjects("Chart1").delete()
I am running into trouble converting the
ActiveChart.Paste
method. I can't get it to work in VB.NET. It either throws an error or It just leaves an empty box when I do it in VB.NET (if I add.chart
before the paste it runs, but doesn't paste any values), but in VBA it fills in the values of interest. I have tried creating a chart object, but that did not seem to work either.I feel like I am close to having it sorted out, but I can't quite get it. I suppose I could leave it as a VBA macro and call it from VB.NET, but that seems absurd on some level. Any help would be greatly appreciated. I am also open to different approaches, its just this is the thing I came across that worked well in VBA, so I figured it was a good starting point.
Thanks as always!