How to create Excel Graph using Access VBA?

K

keirnus

Registered User.
Local time
Tomorrow, 04:00
Joined
Aug 12, 2008
Messages
99
Hello,

I'm really new to this "graph" stuff.

I couldn't google the topics regarding the creation of Graph
in Excel using Access VBA...Dunno where to start. (huhuhu)

What I only know is opening the Excel file.
That's all.

Can anyone help me?
Or point me to some helpful sites to start with?

Help will really be greatly appreciated.
 
tranchemontaigne said:
Craig Dolphin just posted sample code for this ...


http://www.access-programmers.co.uk/forums/showthread.php?&p=742348#post742348
Click to expand...

Thanks for the post, tranchemontaigne.

I checked Craig's code and got interested on the Chart Type:
Code: 
Set xlChartObj = xlApp.Charts.Add
xlChartObj.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"

It doesn't seem to work in my Access.
I think I'm missing something.

Anyway, this is the code I am making now:
Code: 
    'Start Excel and create a new workbook
    Set oXL = CreateObject("Excel.application")
    Set oBook = oXL.Workbooks.Add
    Set oSheet = oBook.Worksheets.Item(1)
    
    ' Insert Random data into Cells for the two Series:
    Randomize Now()
    For iRow = 1 To cNumRows
       For iCol = 1 To cNumCols
          aTemp(iRow, iCol) = Int(Rnd * 50) + 1
       Next iCol
    Next iRow
    oSheet.Range("A1").Resize(cNumRows, cNumCols).Value = aTemp
    
    'Add a chart object to the first worksheet
    Set oChart = oSheet.ChartObjects.Add(50, 40, 300, 200).Chart
    oChart.SetSourceData Source:=oSheet.Range("A1").Resize(cNumRows, cNumCols)
    'oChart.Type = xl3DColumn
    oChart.HasTitle = True
    oChart.ChartTitle.Text = "Chart Title Here"
    ' Make Excel Visible:
    oXL.Visible = True
    oXL.UserControl = True

I got this from DJkarl's link which is the microsoft support site.

I commented this line
Code: 
'oChart.Type = xl3DColumn
because it got an error.

I already have the data. It is dynamic and not coming from Access DB.
Thus, it is a dynamically computed data based from Access DB data.

What I want is to display two graphs with different types:
[1] Standard Type : XY (Scatter) : "Scatter with data points conneted by lines"
[2] Custom Type : Line - Column

I also need to display the legends at the bottom and the values per column per legend.

Can somebody help me on how to do this?
 
Your best bet is to go into excel, start recording a macro, then make and format the chart you want.

Stop recording the then take a look at the vba in the macro. Take that vba and use it inside the access vba module after you're opened the excel object etc as per the code I posted. That should ensure that you are using the correct terms.

Also, you need to ensure you're turned on the MS Excel vba reference in the vba editor.

Good luck with it.
 
CraigDolphin said:
Your best bet is to go into excel, start recording a macro, then make and format the chart you want.

Stop recording the then take a look at the vba in the macro. Take that vba and use it inside the access vba module after you're opened the excel object etc as per the code I posted. That should ensure that you are using the correct terms.

Also, you need to ensure you're turned on the MS Excel vba reference in the vba editor.

Good luck with it.
Click to expand...

Thanks for the reply, Craig.
You're the one who helped tranchemontaigne. :)

But I don't know yet how to display the legends and the respective values in Excel yet. :( I'll try asking around. Once I know how to create in Excel, I'm sure I can using Access VBA.
 
Hello Craig,

Was able to make macro already.

But there are things from Excel macro that are not understood by Access VBA. Such thing like the one below.

From Excel:
Code: 
    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"

Access VBA:
Code: 
    Dim oChart As Object     ' Excel Chart

    'Add a chart object to the first worksheet
    Set oChart = oSheet.ChartObjects.Add(50, 60, 500, 300).Chart

    oChart.ChartType = 21   'ERROR Here

I used "21" in Access VBA because it is the value displayed in Excel VB Editor when debugged.

Now, I'm stuck in setting the Chart Type in Access VBA.

How to set Char Types in Access VBA?

Been looking for the equivalent for this Char Types in Access VBA
and haven't found so far. And there are more than 1 Char Types in reality.

I'm hoping someone could lead me to a list of the equivalent values of Excel Char Types in Access VBA.
 
keirnus said:
Hello Craig,

Was able to make macro already.

But there are things from Excel macro that are not understood by Access VBA. Such thing like the one below.

From Excel:
Code: 
    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"

Access VBA:
Code: 
    Dim oChart As Object     ' Excel Chart
 
    'Add a chart object to the first worksheet
    Set oChart = oSheet.ChartObjects.Add(50, 60, 500, 300).Chart
 
    oChart.ChartType = 21   'ERROR Here

I used "21" in Access VBA because it is the value displayed in Excel VB Editor when debugged.

Now, I'm stuck in setting the Chart Type in Access VBA.

How to set Char Types in Access VBA?

Been looking for the equivalent for this Char Types in Access VBA
and haven't found so far. And there are more than 1 Char Types in reality.

I'm hoping someone could lead me to a list of the equivalent values of Excel Char Types in Access VBA.
Click to expand...

If you are new at developing with Excel I'd suggest referencing the Excel library so you can declare actual Excel objects instead of generic objects.

Code: 
Dim eApp as Excel.Application
Dim eBook as Excel.WorkBook
Dim eSht as Excel.Worksheet

This gives you the intellisense for what you're working with and makes developing a little less painful.
 
Keirnus,

Sorry. I don't know of a list such as the one you asked for. Maybe try Excel's vba help for those numerical equivalents.

I really never got the approach in M$'s example to work for me.

The approach I took came from somewhere else, originally, and it works for me. DJkarl has the right of it, IMO. It makes much more sense to use Access' built-in library of Excel objects and commands. As I said earlier, all it requires is turning on the appropriate vba reference in the vba editor, and using a variation on the code I posted.

But if you really want to use M$'s code example then I'd be very interested to see your final code and how it works for you :) That way we all learn :)

As I stated in the other thread, I did a lot of searching and tinkering before I found the approach that worked for me. And I'm not a code guru like some of the folks around here so I tend to find my way by muddling through. :eek:
 
CraigDolphin said:
Keirnus,

Sorry. I don't know of a list such as the one you asked for. Maybe try Excel's vba help for those numerical equivalents.

I really never got the approach in M$'s example to work for me.

The approach I took came from somewhere else, originally, and it works for me. DJkarl has the right of it, IMO. It makes much more sense to use Access' built-in library of Excel objects and commands. As I said earlier, all it requires is turning on the appropriate vba reference in the vba editor, and using a variation on the code I posted.

But if you really want to use M$'s code example then I'd be very interested to see your final code and how it works for you :) That way we all learn :)

As I stated in the other thread, I did a lot of searching and tinkering before I found the approach that worked for me. And I'm not a code guru like some of the folks around here so I tend to find my way by muddling through. :eek:
Click to expand...

Yeah, I like to try DJkarl's advice.
Seems like painless to me too. :)

Never tried referencing Excel Libraries yet. :(
But I will, that's when i wake up and am in the office.
Coz it's 2:07am already here. :D
(played DotA, that's why...hee!)

Thanks for the advice Craig and DJkarl.

Wish me luck tomorrow...I mean later today. (lol)

Good night everyone.

zzzzzzZZZZZ
 
Hello,

I'm back. :)
I (now) am able to create a graph chart in Excel using Access VBA. (yey!)
I referenced the Excel Library like what DJkarl and Craig said.
...and it blows away my headache. :D
Works like MAGIC! :cool:

I was able to create the following chart types:
[1] Standard Type : Line
- Line with markers displayed at each data value
[2] Custom Type : Line - Column
- Classic combination chart. Line series and column series plotted on the same axis

But in the Custom Type : Line - Column Chart Type...
I created a graph in macro and it displays like what I want.
(Please refer to attached image file)
chartmdokcf7.png


But when I transfered the macro to Access VBA,
it has a different display and is not what I want. :(
(Please refer to attached image file)
chartmdngcf9.png


The 2 lines of the Accumulated Series are converted to bars, the same with the Plan and Actual. :(

(Please ignore the data in the accumulated series.
They are just dummies and not the correct data.)

Here's my Access VBA code:
Code: 
Private Sub cmdMDGraph_Click()
 
    Dim oXL As Excel.Application    ' Excel application
    Dim oBook As Excel.Workbook     ' Excel workbook
    Dim oSheet As Excel.Worksheet   ' Excel Worksheet
    Dim oChart As Excel.Chart       ' Excel Chart
 
    Dim iRow As Integer      ' Index variable for the current Row
    Dim iCol As Integer      ' Index variable for the current Row
 
    Const cNumCols = 10      ' Number of points in each Series
    Const cNumRows = 5       ' Number of Series
 
    ReDim aTemp(1 To (cNumRows + 1), 1 To cNumCols)
 
    'Start Excel and create a new workbook
    Set oXL = CreateObject("Excel.application")
    Set oBook = oXL.Workbooks.Add
 
    Set oSheet = oBook.Worksheets.Item(1)
 
    ' Insert data into Cells for the two Series:
    For iCol = 1 To cNumCols
       aTemp(1, iCol) = "A" & iCol
       aTemp(2, iCol) = 1
       aTemp(3, iCol) = 1 + 2
       aTemp(4, iCol) = iCol
 
       If (iCol * 2) <= cNumCols Then
        aTemp(5, iCol) = iCol * 2
       Else
        aTemp(5, iCol) = cNumCols
       End If
    Next iCol
 
    oSheet.Range("A1").Resize(5, cNumCols).Value = aTemp
 
    oSheet.Name = "MyChart_MD"
 
    Set oChart = oXL.Charts.Add
 
    oChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
    oChart.SetSourceData Source:=oSheet.Range("A1").Resize(5, 4), PlotBy:=xlColumns
 
    oChart.SeriesCollection(1).XValues = oSheet.Range("A1").Resize(1, cNumCols)
    oChart.SeriesCollection(1).Values = oSheet.Range("A2").Resize(1, cNumCols)
    oChart.SeriesCollection(1).Name = "Plan"
    oChart.SeriesCollection(2).Values = oSheet.Range("A3").Resize(1, cNumCols)
    oChart.SeriesCollection(2).Name = "Actual"
    oChart.SeriesCollection(3).Values = oSheet.Range("A4").Resize(1, cNumCols)
    oChart.SeriesCollection(3).Name = "Accumulated Plan"
    oChart.SeriesCollection(4).Values = oSheet.Range("A5").Resize(1, cNumCols)
    oChart.SeriesCollection(4).Name = "Accumulated Actual"
    oChart.Location Where:=xlLocationAsNewSheet, Name:="chartMD"
 
    With oChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Chart : MD"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Accumulated"
        '.Axes(xlCategory, xlSecondary).HasTitle = False
        '.Axes(xlValue, xlSecondary).HasTitle = False
    End With
    oChart.HasLegend = False
    oChart.HasDataTable = True
    oChart.DataTable.ShowLegendKey = True
 
    ' Make Excel Visible:
    oXL.Visible = True
    oXL.UserControl = True
 
    'clean-up
    Set oXL = Nothing: Set oChart = Nothing: Set oSheet = Nothing: Set oBook = Nothing
 
End Sub

I commented the following lines because it returns error:
Code: 
        '.Axes(xlCategory, xlSecondary).HasTitle = False
        '.Axes(xlValue, xlSecondary).HasTitle = False

Do these lines have something to do with the display in the graph?
If so, how to pass the said lines without any errors?

Help is really, greatly appreciated.
 
I think I now know why the 2nd image is not displaying the 2 lines like the 1st image.

Setting the Chart Type is done by this line of code:
Code: 
    oChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"

I debugged the oChart and found out that after passing the above line,
the Chart Type set is xlColumnClustered which is not Line-Column.

What I want is to set Custom Type : Line - Column as the Chart Type.
I also want to set the Custom Type : Line - Column 2 Axes.

How to set these kind of Chart Types in Access VBA?
 
I got it now!

hahaha! :D

So, stupid of me for not knowing the sequence.

SOLUTION:
The source data must be set first before setting the Chart Type.

Thus,
Code: 
    oChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
    oChart.SetSourceData Source:=oSheet.Range("A1").Resize(5, 4), PlotBy:=xlColumns

...is changed into
Code: 
    oChart.SetSourceData Source:=oSheet.Range("A1").Resize(5, 4), PlotBy:=xlColumns
    oChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"

Eureka! :cool:

Now, I can continue with my work. :D

Thanks to Craig and DJkarl for all the advices. :)

Without them, the world is indeed (always) cruel...nyehehe!
 
I don't know if it makes any difference, but the charts produced by my code are column -line as per your first example (see attached for what my chart output looks like).

I have
With xlChartObj
.SetSourceData source:=xlSourceRange, PlotBy:=xlColumns
.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"

where you have it reversed...perhaps setting them in reverse order does something unexpected?
 

Attachments

  • chartexample.JPG
    chartexample.JPG
    72.6 KB · Views: 1,123
oops...I see you already figured it out :) Good thing to know :)
 
CraigDolphin said:
I don't know if it makes any difference, but the charts produced by my code are column -line as per your first example (see attached for what my chart output looks like).

I have
With xlChartObj
.SetSourceData source:=xlSourceRange, PlotBy:=xlColumns
.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"

where you have it reversed...perhaps setting them in reverse order does something unexpected?
Click to expand...

The first example works on you? :eek:
That's strange. :confused:

The order affects in mine.
But dunno why.

Will post something here if I get answers on that one.
 
Ooops!

I sent it twice.

Sorry.

Please refer to my post before this.
 
Last edited:
Perhaps I phrased it poorly. Just to clarify. My original code produces a chart (attached to post 15) which is a line - colmn chart type. The first example of yours that I was referring to was the first example chart - also a line column chart type.

I was suggesting you set the chart source first, then chart type. But you apparently found the solution and posted it while I was typing. :D
 
CraigDolphin said:
Perhaps I phrased it poorly. Just to clarify. My original code produces a chart (attached to post 15) which is a line - colmn chart type. The first example of yours that I was referring to was the first example chart - also a line column chart type.

I was suggesting you set the chart source first, then chart type. But you apparently found the solution and posted it while I was typing. :D
Click to expand...

ah, ok...hehehe! :D

Thanks anyway. :)
 
You must log in or register to reply here.

Similar threads

N
Visualising data in Access
Replies
3
Views
392
tvanstiphout
T
Mylton
Solved Best way to protect VBA code in Excel
2
Replies
20
Views
565
Mylton
Mylton
Mario R. Perez
App Integration AutoZone
Replies
2
Views
108
GPGeorge
GPGeorge
H
append data from excel to existing table in access vba
Replies
6
Views
601
Pat Hartman
P
Z
Setting on click event of a control in VBA
Replies
8
Views
344
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom