INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Visual Basic (Microsoft) Versions 5/6 FAQ

Integration With MS Excel

How to insert a chart into excel from VB by Phathi
Posted: 21 Aug 01

After much great advice and tips from various members, I thought I might share with you how I solved this problem.

The below block needs for you to reference Excel.


-- start block

Dim AppExcel As New Excel.Application
Dim xlDoc As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim objChart As ChartObject  
Dim xlSeries As SeriesCollection

Dim strName() As String
Dim strXAxis As String

Dim strSQL As String
Dim intHeight As Integer

strSQL = "Select * From DB_Table "

Set xlDoc = AppExcel.Workbooks.Add
Set xlSheet = xlDoc.Worksheets(1)

Set m_rsData = New ADODB.Recordset

With m_rsData
        .Open strSQL, OraDb
    redim strName(.Fields.Count) as String
    'This Gives Titles in a down fashion.
    'To go across, one must go B1,C1,D1 for which I do not have a loop
    for iLoop=1 to .Fields.Count-1
        xlSheet.Range("B" & .Fields(iLoop).Name & ).Cells=.Fields(iLoop).Name
    next iLoop
    
    intHeight = 5
        While Not .EOF
                xlSheet.Range("B" & intHeight).Cells = Format$(.Fields("SOME_DATE"), "ddd MMM d")
            xlSheet.Range("C" & intHeight).Cells = .Fields(1) & " "
        xlSheet.Range("D" & intHeight).Cells = .Fields(1) & " "
        xlSheet.Range("E" & intHeight).Cells = .Fields(1) & " "
        xlSheet.Range("F" & intHeight).Cells = .Fields(1) & " "
        xlSheet.Range("Z" & intHeight).Cells = .Fields(1) & " "
        intHeight = intHeight + 1
                .MoveNext
    Wend
    .Close
End With

' Add a ChartObject to the worksheet:
Set objChart = xlSheet.ChartObjects.Add(100, 100, 500, 200)

' Insert Named Ranges:
    strTmpRange = "R5C3:R" & Format$(intHeight - 1) & "C3"
    xlSheet.Parent.Names.Add "Range1", "=Sheet1!" & strTmpRange
    strName(1) = "=Sheet1!R3C3"
       
    strTmpRange = "R5C4:R" & Format$(intHeight - 1) & "C4"
    xlSheet.Parent.Names.Add "Range2", "=Sheet1!" & strTmpRange
    strName(2) = "=Sheet1!R3C4"
'etc

'start the series collection
Set xlSeries = objChart.Chart.SeriesCollection

For iCount = 1 To 2 'the number of named ranges (which should be the number of fields)
    xlSeries.Add "Range" & iCount
        xlSeries.Item(iCount).Name = strName(iCount)
Next iCount

Set m_rsData = Nothing

exit sub


-- end block

you may also use the chartwizard after adding a chart
object

objChart.Chart.Chartwizard Arguments

where Arguments is

1.Source: Optional Variant. The range that contains the source data for the new chart. If this argument is omitted, Microsoft Excel edits the active chart sheet or the selected chart on the active worksheet.

2.Gallery: Optional Variant. The chart type. Can be one of the following XlChartType constants: xlArea, xlBar, xlColumn, xlLine, xlPie, xlRadar, xlXYScatter, xlCombination, xl3DArea, xl3DBar, xl3DColumn, xl3DLine, xl3DPie, xl3DSurface, xlDoughnut, or xlDefaultAutoFormat.
Format: Optional Variant. The option number for the built-in autoformats. Can be a number from 1 through 10, depending on the gallery type. If this argument is omitted, Microsoft Excel chooses a default value based on the gallery type and data source.

3.PlotBy: Optional Variant. Specifies whether the data for each series is in rows or columns. Can be one of the following XlRowCol constants: xlRows or xlColumns.
CategoryLabels: Optional Variant. An integer specifying the number of rows or columns within the source range that contain category labels. Legal values are from 0 (zero) through one less than the maximum number of the corresponding categories or series.

4.SeriesLabels: Optional Variant. An integer specifying the number of rows or columns within the source range that contain series labels. Legal values are from 0 (zero) through one less than the maximum number of the corresponding categories or series.
 
5.HasLegend: Optional Variant. True to include a legend.

6.Title: Optional Variant. The chart title text.

7.CategoryTitle: Optional Variant. The category axis title text.

8.ValueTitle: Optional Variant. The value axis title text.

9.ExtraTitle: Optional Variant. The series axis title for 3-D charts or the second value axis title for 2-D charts.

Back to Visual Basic (Microsoft) Versions 5/6 FAQ Index
Back to Visual Basic (Microsoft) Versions 5/6 Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close