Graph in excel from access VB

[sonikudi]

Hi,

I have query in access that i output in excel and want to graph the output. I have written the code for this in access -> visual basic

i want the code to get the range of the xvalues and the yvalues from the output query results in excel sheet and graph column B vs Column A...


For the graphing part of the code this is what i have:



' Create a new chart.
Set xlChartObj = xlApp.Charts.Add


With xlChartObj

' Specify chart type as 3D.
.ChartType = xlLineMarkers ' Set the range of the chart.
.SetSourceData Source:=xlSourceRange

'_____________________________________________________

.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = HOW DO I GET THE RANGE OF THE XVALUES thats in column A of the excel sheet???
.SeriesCollection(1).Values = HOW DO I GET THE Y values that is column B???
'______________________________________________________
' Specify that the chart is located on a new sheet.
.Location Where:=xlLocationAsNewSheet



ANy help will me much appreciated.

THANKS!!

 

[SkipVought]

    .SeriesCollection(1).XValues = sheets("yoursheetname").range(sheets("yoursheetname").[A1], sheets("yoursheetname").[A1].end(xldown))
    .SeriesCollection(1).Values = sheets("yoursheetname").range(sheets("yoursheetname").[B1], sheets("yoursheetname").[B1].end(xldown))

assuming that your DATA starts in ROW 1.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[sonikudi]

Hi Skip,


Firstly, Thanks so much for suggesting a solution!!

I did try the lines of code but it gives me an error saying 'subscript out of range'

The data in excel starts from row 2, following is the code...


' Create a new chart.
Set xlChartObj = xlApp.Charts.Add


With xlChartObj

' Specify chart type as 3D.
.ChartType = xlLineMarkers ' Set the range of the chart.
' .SetSourceData Source:=xlSourceRange
', PlotBy:=xlColumns

'_____________________________________________________

.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = Sheets("Order_Subtotals").Range(Sheets("Order_Subtotals"
.[A2], Sheets("Order_Subtotals").[A2].End(xlDown))
.SeriesCollection(1).Values = Sheets("Order_Subtotals").Range(Sheets("Order_Subtotals")
.[B2], Sheets("Order_Subtotals").[B2].End(xlDown))
'_______________________________________________________
' Specify that the chart is located on a new sheet.
.Location Where:=xlLocationAsNewSheet

' Create and set the title; set title font.
.HasTitle = True
With .ChartTitle
.Characters.Text = _
"Subtotals by OrderID"
.Font.Size = 12
' .Font.FontStyle = "Bold" code to make it bold
End With



HELP!!!

 

[sonikudi]

okay i did it differently and it worked!!! For those that ever have the same problem..Following is the code



Skip thanks to you as well!!

___________________________________________________________
Private Sub Command2_Click()

Dim strSourceName As String
Dim StrFileName As String
Dim xlApp As Excel.Application
Dim xlWrkbk As Excel.Workbook
Dim xlSourceRange As Excel.Range
Dim srsNew As Excel.Series
Dim xlColPoint As Excel.Point
Dim iDataRowsCt As Long
Dim iDataColsCt As Integer

strSourceName = "Order Subtotals"
StrFileName = "C:\Sales.xls"
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
strSourceName, StrFileName, False

DoCmd.OutputTo acOutputQuery, strSourceName, acFormatXLS, StrFileName, False


'Create a Microsoft Excel object.
Set xlApp = CreateObject("Excel.Application")
' Open the spreadsheet to which you exported the data.
Set xlWrkbk = xlApp.Workbooks.Open(StrFileName)


Set xlSourceRange = xlWrkbk.Worksheets(1).Range("a1").CurrentRegion
With xlSourceRange
iDataRowsCt = .Rows.Count
iDataColsCt = .Columns.Count
End With



' Create a new chart.
Set xlChartObj = xlApp.Charts.Add


With xlChartObj

' Specify chart type as 3D.
.ChartType = xlLineMarkers ' Set the range of the chart.

'' Remove any series created with the chart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop

For iSrsIx = 1 To iDataColsCt - 1
'' Add each series
Set srsNew = .SeriesCollection.NewSeries
With srsNew
.Name = xlSourceRange.Cells(1, iSrsIx)
.Values = xlSourceRange.Cells(2, iDataColsCt) _
.Resize(iDataRowsCt - 1, 1)
.XValues = xlSourceRange.Cells(2, iSrsIx) _
.Resize(iDataRowsCt - 1, 1)
End With
Next



' Specify that the chart is located on a new sheet.
.Location Where:=xlLocationAsNewSheet

' Create and set the title; set title font.
.HasTitle = True
With .ChartTitle
.Characters.Text = _
"Subtotals by OrderID"
.Font.Size = 12
' .Font.FontStyle = "Bold" code to make it bold
End With

End With

' Save and close the workbook
' and quit Microsoft Excel.
With xlWrkbk
.Save
.Close
End With
xlApp.Quit


xlApp.Visible = True
xlApp.Workbooks.Open (StrFileName)

Exit_CreateChart:
Set xlSourceRange = Nothing
Set xlColPoint = Nothing
Set xlChartObj = Nothing
Set xlWrkbk = Nothing
Set xlApp = Nothing



End Sub

 

[SkipVought]

This does work.

Make sure that the sheet containing the chart source data is named EXACTLY, Order_Subtotals

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}