Question Draw chart in excel

[vijay2482]

Hi all

I have a ms access database with few tables and queries. I need to draw a graph/chart with the results of the queries in excel.
Is it possible to do with vb.net coding?
If possible, any suggestion and references will be very much helpful.

Thanks in advance.

 

[Budius]

yes, it is possible.
and instead of anyone giving you a code ready you can create it yourself, it's simple.

- After you have the data on excel go to developer tab (you might need to enable it on the Excel options) and click 'Record Macro'
- Create the chart the way you need manually
- Stop recording the macro.
- Click in "Visual Basic" in the developer tab and check the code there.
- Adapt it for your application!


ps.: if you're using excel 2003 or less there is no developer tab. Those options will be in some menu (probably tools)

 

[JohnH]

I agree with Budius here, the built in macro language VBA in Excel uses the Excel Object Library, the same library can be used in VB.Net to automate Excel. So the code you get when doing tasks in Excel while recording macro is very much the same code you use when automating Excel from VB.Net. I will give an example of this. Let's say you have an existing Excel book with a Sheet1 containing some data in two columns and header in first row. If you record macro and add a default chart as new sheet for that data range you get this VBA code or similar:

Charts.Add
With ActiveChart
    .ChartType = xlColumnClustered
    .SetSourceData Source:=Sheets("Sheet1").Range("A1:B11"), PlotBy:=xlColumns
    .Location Where:=xlLocationAsNewSheet
    .HasTitle = False
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = False
End With

Now in VB.Net to do automation you first add reference to the relevant version of COM library "Microsoft Excel Object Library". Then to simplify type qualifications import the namespace:

Imports Microsoft.Office.Interop.Excel

Here's the translation, only a little bit modified. The main difference is types and references are more explicit (though this sample uses Option Strict Off, late binding):

Dim app As New Application
app.Visible = True ' False to not show Excel
Dim book = app.Workbooks.Open("c:\Book1.xls")
Dim sheet = book.Worksheets("Sheet1")

'create chart
book.Charts.Add()
With book.ActiveChart
    .ChartType = XlChartType.xlColumnClustered
    .SetSourceData(Source:=sheet.Range("A1").Resize(11, 2), PlotBy:=XlRowCol.xlColumns)
    .Location(XlChartLocation.xlLocationAsNewSheet)
    .HasTitle = False
    .Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary).HasTitle = False
    .Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary).HasTitle = False
End With

' uncomment these to save and close book and quit the app:
'book.Save()
'book.Close()
'app.Quit()

Instead of using Range("A1:B11") as in generated VBA code I used a starting cell "A1" and expanded the range selection with the Resize method, this works better with dynamic data, where you more easily can set column and row sizes. For example from a DataTable you have the .Columns.Count and .Rows.Count values that can be used.

There exist many examples of transferring data to Excel using VB.Net, here for example is an article that discusses both automation and ADO.Net (OleDb) methods for doing that: How to transfer data to an Excel workbook by using Visual Basic .NET