Creating a graph for every row of data using Excel exported by Microsoft Access coded in VBA

doubllehellix

New Member
Joined
Oct 18, 2017
Messages
1
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. :cool:


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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,144,158
Messages
5,722,824
Members
422,460
Latest member
VBA_Noob01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top