Results 1 to 3 of 3
  1. #1
    doubllehellix is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    2

    Unhappy Exporting Access tables to Excel using VBA and creating a 2d stacked area chart

    I need to graph 2D stacked area graphs, so this is how I am trying to do it.


    I'm using Microsoft Access exporting with VBA into excel and trying to create a 2d stacked area graph based upon two different imported tables.

    The best part, is I need a separate graph for each row of data being compared on sheet 1 and 2.

    Please help me guys in this example I named the. "Backwards" is one table and "Forwards" is another.

    I thought I was smart until I encountered this code, anyone who can solve this is definitely smart.



    Code:
    Private Sub Command0_Click()
    
    
    Dim Oxl As Object
    Set Oxl = CreateObject("Excel.Application")
    Dim i As Long
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim chrt As Chart
    
    
    
    
    
    
    
    
    DoCmd.TransferSpreadsheet acExport, _
    acSpreadsheetTypeExcel12Xml, "Backwards Graph", _
    "C:\Users\me\Desktop\Graph.xlsx", , "Backwards"
    
    
    DoCmd.TransferSpreadsheet acExport, _
    acSpreadsheetTypeExcel12Xml, "Forwards Graph", _
    "C:\Users\sdew\Desktop\MBR DATABASE\Grapher.xlsx", , "Forwards"
    
    
    MsgBox "File exported successfully", vbInformation + vbOKOnly, "Export Success"
    
    
    
    
    'Find the last row used
    LastRow = Sheets("Backwards").Range("A65536").End(xlUp).Row
    
    
    'Find the last column used
    LastColumn = Sheets("Backwards").Range("A1").End(xlToRight).Column
    
    
    'Looping from second row till last row which has the data
    
    
    For i = 2 To LastRow
            
            'Graph1 is selected charts will be inserted here
            Sheets("Backwards").Select
            
            'Add chart to sheet
            Set chrt = Sheets("Backwards").Shapes.AddChart.Chart
                'sets the chart type
                chrt = CharType = xlLine
                
                'now the line chart is added... Setting its data source here
                
            With Sheets("Backwards")
                chrt.SetSourceData Source:=.Range(.Cells(i, 1), .Cells(i, LastColumn))
                
            End With
            
            'left & top are used to adust the poistion of the chart on sheet
            chrt.ChartArea.Left = 1
            chrt.ChartArea.Top = (i - 2) * chrt.ChartArea.Heights
            
            Next
        
    
    
    
    
    End Sub

  2. #2
    Join Date
    Apr 2017
    Posts
    1,230
    How to use such difficult path?

    In Excel, use ODBC Query/Queries to read data from tables or from saved query in your access database. Set the query to be refreshed on opening.

    Also in Excel, design a graph based on query table(s). Use dynamic named ranges in graph so it adjusts automatically to retrieved query table(s).

    Whenever the user opens the excel workbook, he/she gets fresh chart. and of-course user can refresh data manually at ay time.

  3. #3
    doubllehellix is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    2
    Quote Originally Posted by ArviLaanemets View Post
    How to use such difficult path?

    In Excel, use ODBC Query/Queries to read data from tables or from saved query in your access database. Set the query to be refreshed on opening.

    Also in Excel, design a graph based on query table(s). Use dynamic named ranges in graph so it adjusts automatically to retrieved query table(s).

    Whenever the user opens the excel workbook, he/she gets fresh chart. and of-course user can refresh data manually at ay time.

    The graph is going to be embedded into an access form, and access report. I need it to dynamically update and compare two separate tables in a stacked 2d area chart. I already have an algorithm for exporting the two tables onto different sheets in excel,

    literally all I need to do is graph each row of data

    I know nothing about ODBC Query/ Quries, and the idea is to automate the process so that the used enters data in the form clicks a button and the stacked area graph automatically compares the corresponding rows from each table and creates a graph from each row.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 09-29-2017, 11:53 AM
  2. Replies: 7
    Last Post: 02-18-2014, 03:36 PM
  3. Problem Creating Excel Chart TextBoxes in Access 2010
    By JonMulder in forum Programming
    Replies: 5
    Last Post: 03-24-2013, 08:16 PM
  4. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  5. Replies: 1
    Last Post: 05-04-2012, 02:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums