1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Calling excel functions from access

Discussion in 'Business Applications' started by Shlaga, Nov 7, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. Shlaga

    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
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
  3. Shlaga

    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!
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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
     
  5. Shlaga

    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
     
  6. Sponsor

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 733,556 other people just like you!

Loading...
Similar Threads - Solved Calling excel
  1. kramerica
    Replies:
    4
    Views:
    268
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/649046

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice