Solved: Calling excel functions from access

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Shlaga

Thread Starter
Joined
Oct 23, 2007
Messages
41
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 be greatly appreciated!!

Thanks
 

Shlaga

Thread Starter
Joined
Oct 23, 2007
Messages
41
hi firefytr,

Firstly, THANKS for suggesting a solution my problem!:)

my data is located in access Db , so i am doing a query on the table and outputting it to to excel and then graph in excel as well. For the range this is line of code i entered

'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

this code followed by the one i pasted earlier for 'create a new chart'



The solution you suggested

= "='Your Sheet Name Here!R2C1:R10C1"

What does R2C1:R10C1 refer to?
is it row 2 column 1 to row 10 column1?? If yes then what if my data uses 15 rows instead of 10 rows? The output varies and i don't always have a consistent number of rows.




thanks!
 
Joined
Jul 25, 2004
Messages
5,456
It is an R1C1 reference style to a range. It says Row 2 of Column 1, which is A2, intersection to Row 10 Column 1, which is A10. So that range is actually A2:A10 in normal style. With your code you could use ..

Code:
= "='" & xlSourceRange.Parent.Name & "'!" & xlSourceRange.Address(True, True, xlR1C1)
HTH
 

Shlaga

Thread Starter
Joined
Oct 23, 2007
Messages
41
firefytr...THANK YOU SO MUCH!!

i didn't try the R1C1 method but i did later go to the website you referred me to and saw something similar that used a for loop which i am more comfortable with...

Here is the code for those of you who stumble upon this thread and might want to do the same...


________________________________________

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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top