Programming Section

In this section you can find some examples of code to implement small web or windows applications. All the code in these web pages has Creative Commons License. If you want more information about this section or want to suggest some modifies don't hesitate to contact me (Contact me).

How to extract charts from an excel spreadsheet whit VB.NET

In this article I am going to show you a VB.NET script that allows you to extract charts from an excel file. You have to use Visual Studio Express (or payment versions).
A VB.NET console application is used in order to implement this project but if you want you can also use a Windows Form Application to have an user friendly interface (see download area).
First of all, you have to add the following reference to your project: Microsoft.Office.Interop.Excel. You can add this Extension in this way: go to Project -> Add Reference -> .NET and select from the list the Extension.
The next step is to insert the following code:


Imports Microsoft.Office.Interop.Excel

Module Module1
    Sub Main()

        Dim excel As New Application
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim chart As Chart
 
        wb = excel.Workbooks.Open("C:\... file path")

        For y = 1 To wb.Worksheets.Count
            ws = wb.Worksheets.Item(y)
            For i = 1 To ws.ChartObjects.Count
                chart = ws.ChartObjects(i).Chart
                chart.Export("C:\... Output folder path ...\Worksheets " &        
                              y.ToString & "Chart " & i.ToString & ".png", "PNG")
            Next i
        Next y

        excel.Workbooks.Close()
        excel.Quit()
    End Sub

End Module
				

The first code line need to import the "Excel" namespace names. Thus, you can instantiate the "Application" object and create a reference to the following objects: "Workbook", "Worksheet" and "Chart".
Now you can use the "Workbooks.Open" Method (in more detail: this method creates a new "workbook" object and returns it) in order to open the excel file you want.
The first "For...Next" Statement allows to "open" all the worksheets included in the workbook opened in the previous step. In fact, the "Count" Method is used to return the number of elements in the "Worksheets" Collection.
The next step is to use the "Worksheets.Item" Property in order to return a single "Worksheet" Object from the "Worksheets" Collection.
The second "For...Next" Statement allows to export all the charts included in each worksheet. In fact, the "Count" Method is used to return the number of elements in the "ChartObjects" Collection.
Thus the "Worksheets.ChartObjects" Method selects the chart number "i" and the "Chart.Export" Method xports the chart in a graphic format in a defined path.
At the end of the code you have to use the "Workbooks.Close" Method to close the Workbook and the "Application.Quit" Method to quit Microsoft Excel.

If you find errors in this code, I will be very grateful if you report them to me. Thanks!