[RESOLVED] Exporting Image From Excel

[Siddharth Rout]

Hello

This code works in VBA

http://www.vbforums.com/showthread.php?t=538529

However, when I try it from vb.net, I get the following error. (Image attached)

The code that I am trying is as follows

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim olobj As Excel.Shape
        Dim xlCharts As Excel.ChartObjects
        Dim myChart As Excel.ChartObject
        Dim I As Long = 1
        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Open("C:\Test.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
        xlCharts = xlWorkSheet.ChartObjects
        myChart = xlCharts.Add(10, 80, 300, 250)

        '<~~ There could be more pictures than one
        For Each olobj In xlWorkSheet.Shapes
            olobj.Copy()
            myChart.Activate()
            myChart.Paste()
            myChart.Export("C:\Image" & I & ".gif", "FilterName:=GIF", True)
            I = I + 1
            '<~~ code to delete the picture from Chart and make it ready
            '<~~ for next picture
            '<~~(STILL STUCK ON THIS. WILL TACKLE IT AFTER THE ABOVE IS SORTED)
        Next
        xlWorkBook.Close(SaveChanges:=False)
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

Thanks for looking into it.

Sid

[Pradeep1210]

Excel ChartObject does not have a Paste property. Try myChart.Chart.Paste() instead.
There is also an error in the immediate next line and that needs to be corrected too.

myChart.Chart.Paste()
myChart.Chart.Export("C:\Temp\Image" & I & ".gif", "GIF", True)

[Pradeep1210]

Try this code. It will also delete the unwanted image as required in your comments:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim olobj As Excel.Shape
    Dim xlCharts As Excel.ChartObjects
    Dim myChart As Excel.ChartObject
    Dim I As Long = 1
 
    xlApp = New Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Open("C:\Temp\Test.xlsx")
    xlWorkSheet = xlWorkBook.Worksheets("Sheet1")
    xlCharts = xlWorkSheet.ChartObjects
    'myChart = xlCharts.Add(10, 80, 300, 250)
 
    '<~~ There could be more pictures than one
    For Each olobj In xlWorkSheet.Shapes
        myChart = xlCharts.Add(10, 80, 300, 250)
        olobj.Copy()
        myChart.Activate()
        myChart.Chart.Paste()
        myChart.Chart.Export("C:\Temp\Image" & I & ".gif", "GIF", True)
        I = I + 1
        myChart.Delete()
    Next
    xlWorkBook.Close(SaveChanges:=False)
    xlApp.Quit()
 
    releaseObject(xlApp)
    releaseObject(xlWorkBook)
    releaseObject(xlWorkSheet)
End Sub

[Siddharth Rout]

Simply Superb!!!!!

I was trying to think what to do to get the initial image off the chart...

Works flawlessly! I knew I could always count on you when it came to vb.net!

Thanks again...

Sid