Consulting

Results 1 to 12 of 12

Thread: From MS Access 2016 TransferSpreadsheet to Excel and format chart to xlBarStacked

  1. #1
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location

    From MS Access 2016 TransferSpreadsheet to Excel and format chart to xlBarStacked

    Hello
    From MS Access, I am learning how to export and format charts to Excel.

    Here I have some VBA code that transfers from MS Access a query and creates a chart into Excel.

    My question is, from the code below, is there any way for it to Format the chart to xlBarStacked and fit the height equal to row lines with data on the query worksheet?

    This is my code:

    Private Sub cmbexpqryxlBarStacked_Click()   
    
    
         Dim wb, ws, xl, ch, qry_createbarstacked as Object
         Dim sExcelWB As String
      
         sExcelWB = TrailingSlash(CurrentProject.Path) & "qry_createbarstacked.xlsx"
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_createbarstacked", sExcelWB, True
    
    
         Set wb = xl.Workbooks.Open(sExcelWB)
         Set ws = wb.Sheets("qry_createbarstacked")
         Set ch = ws.Shapes.AddChart.Chart
         Set mychart = ws.ChartObjects("Chart 1")
    
    
    End Sub
    Thanks very much for the help.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    By referring to mychart you should be able to set it's parameters.
    I would open the chart in Excel and Record a Macro of creating the required setting and then use that VBA code with the necessary adjustments to set them in Access.

  3. #3
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location
    OBP. I really appreciate your reply.
    Yes, I have been running a macro in Excel.
    In a couple of hours, I will send you the code.
    Cheers

  4. #4
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location
    OBP. Hello.
    This is my code baes form a Macro recorded on.

    Option ExplicitSub cmbexpqryxlBarStacked_Click()
    
    
    Dim ch As Chart
    Dim mychart As ChartObject
    Dim ws As Worksheet
    Dim a, b As Range
    
    
        Set ws = Worksheets("qry_createbarstacked")
        Set ch = ws.Shapes.AddChart.Chart
        Set mychart = ws.ChartObjects("Chart 1")
            
             With ch
                .ChartType = xlBarStacked
                .ChartGroups(1).GapWidth = 59
                .ChartArea.Height = 400
                .ChartArea.Width = 700
                .ChartArea.Top = 1
                .SetSourceData Source:=Range("qry_createstackedbarr!$A$1:$E$24")
                .FullSeriesCollection(1).Delete
                .SeriesCollection.NewSeries
                .FullSeriesCollection(1).Values = "=qry_createstackedbarr!$A$2:$A$24"
                .SeriesCollection.NewSeries
                .FullSeriesCollection(2).Values = "=qry_createstackedbarr!$D$2:$D$24"
                .FullSeriesCollection(2).XValues = "=qry_createstackedbarr!$C$2:$C$24"
                .Axes(xlCategory).ReversePlotOrder = True
            End With
        ' Set axis min-max vlues
        Set a = Range("A2:A" & Rows.Count)
            With a
                .Application.WorksheetFunction.Min (a)
            End With    
        Set b = Range("b2:b" & Rows.Count)
            With b
                .Application.WorksheetFunction.Max (b) '
            End With
        
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.Axes(xlValue).MinimumScale = ActiveChart.Application.WorksheetFunction.Min(a)       
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.Axes(xlValue).MaximumScale = ActiveChart.Application.WorksheetFunction.Max(b)    
        ActiveChart.FullSeriesCollection(1).Select
        ActiveChart.ChartArea.Select
        Selection.Format.Fill.Visible = msoFalse
        
            With ActiveSheet.Shapes("Chart 1")
                .Left = Range("F1").Left
                .Top = Range("F1").Top
            End With
        
    'delete stackedbarr bottom 
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.FullSeriesCollection(1).Select
        Selection.Format.Fill.Visible = msoFalse
        ActiveChart.Legend.Delete
        
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.FullSeriesCollection(2).Select
        
            With ActiveSheet.Shapes("Chart 1").Fill
           		.Visible = msoTrue
            	.ForeColor.ObjectThemeColor = msoThemeColorAccent3
            	.ForeColor.TintAndShade = 0
            	.ForeColor.Brightness = 0
           		.Transparency = 0
            	.Solid
        End With
        
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.PlotArea.Select
        
            With Selection.Format.Fill
            	.Visible = msoTrue
           		.ForeColor.ObjectThemeColor = msoThemeColorAccent3
            	.ForeColor.TintAndShade = 0
            	.ForeColor.Brightness = 0
            	.Transparency = 0
            	.Solid
            End With
        
        ActiveChart.FullSeriesCollection(2).Select
            With Selection.Format.Fill
            	.Visible = msoTrue
            	.ForeColor.ObjectThemeColor = msoThemeColorText2
            	.ForeColor.TintAndShade = 0
            	.ForeColor.Brightness = 0.400000006
            	.Transparency = 0
            	.Solid
            End With
        Range("A1").Select
       
    End Sub
    Thanks againg for your help.

  5. #5
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location
    OBP. Hello.
    I have been editing my code.
    Now with parameters referenced to a, b ch and ws my code has fewer lines.
    Also, I avoid using Activesheet, ActiveChart.

    This is my code:
    Option Explicit
    
    Sub cmbexpqryxlBarStacked_Click()
    
    
    Dim ch As Chart
    Dim mychart As ChartObject
    Dim ws As Worksheet
    Dim a, b As Range
    
    
        Set ws = Worksheets("qry_creategantt")
        Set ch = ws.Shapes.AddChart.Chart
        Set mychart = ws.ChartObjects("Chart 1")
            
             With ch
                .ChartType = xlBarStacked
                .ChartGroups(1).GapWidth = 59
                .ChartArea.Height = 400
                .ChartArea.Width = 700
                .ChartArea.Top = 1
                .FullSeriesCollection(1).Delete
                .SeriesCollection.NewSeries
                .FullSeriesCollection(1).Values = Range("A2", Range("A2").End(xlDown))
                .SeriesCollection.NewSeries
                .FullSeriesCollection(2).Values = Range("D2", Range("D2").End(xlDown))
                .FullSeriesCollection(2).XValues = Range("C2", Range("C2").End(xlDown))
                .Axes(xlCategory).ReversePlotOrder = True
            End With
        
        ' Set axis min-max values
        Set a = Range("A2:A" & Rows.Count)
            With a
                .Application.WorksheetFunction.Min (a)
            End With
        Set b = Range("b2:b" & Rows.Count)
            With b
                .Application.WorksheetFunction.Max (b) '
            End With
            
            ws.ChartObjects("Chart 1").Activate
            ch.Axes(xlValue).MinimumScale = ch.Application.WorksheetFunction.Min(a)
            ch.Axes(xlValue).MaximumScale = ch.Application.WorksheetFunction.Max(b)
        
        ch.PlotArea.Select
            With Selection.Format.Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorAccent3
                .ForeColor.TintAndShade = 0
                .ForeColor.Brightness = 0
                .Transparency = 0
                .Solid
            End With
            
            With ws.Shapes("Chart 1")
                .Left = Range("F1").Left
                .Top = Range("F1").Top
            End With
        
    'delete botton stackedbarr
    
    
        ch.FullSeriesCollection(1).Select
        Selection.Format.Fill.Visible = msoFalse
        
        ch.FullSeriesCollection(2).Select
            With ActiveSheet.Shapes("Chart 1").Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorAccent3
                .ForeColor.TintAndShade = 0
                .ForeColor.Brightness = 0
                .Transparency = 0
                .Solid
            End With
            With Selection.Format.Fill
                .Visible = msoTrue
                .ForeColor.ObjectThemeColor = msoThemeColorText2
                .ForeColor.TintAndShade = 0
                .ForeColor.Brightness = 0.400000006
                .Transparency = 0
                .Solid
            End With
            
            
        Range("A1").Select
       
    End Sub
    I would appreciate if you else can help me to refine my code.
    Thank you

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    It is too long ago that I worked on Excel Chart VBA for me to really help you.
    What I would suggest is that if what you have works you "Run" the Macro from your Access Code.
    Use something like

    xl.Run "MyMacro"

  7. #7
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location
    OBP

    The idea you propose does not apply to my project.
    I have to be modifying vba Excel code any time I run the macro from Excel.
    That means over 100 workbooks.

    Cheers



  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Why do you have to modify the code?

  9. #9
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location
    Thank you for your reply.

    In MS Access I have a Form with a textbox to write a value that references the Query.
    Every time I need a new query I have to write another value.


    I'm already googling for ideas in order to have a more efficient code.

    For example, my code by default creates an xlClustered Chart. But, I need to create an xlBarStacked Chart .

    Cheers

  10. #10
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    You could ask your Excel questions on the Excel Forum, rather than the Access one.
    Sorry I could not be of more help.

    ps I have just noticed how early you are up and about, so Good Morning to you.

  11. #11
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location
    OBP.
    Seems that I misunderstood your first reply. I really apologize.
    Here is your first reply:

    "By referring to mychart you should be able to set it's parameters.

    I would open the chart in Excel and Record a Macro of creating the required setting and then use that VBA code with the necessary adjustments to set them in Access."

    I have to thank your suggestion to use mychart as a reference, I tried, however, I found a way to edit my code via ch.

    Thanks.

  12. #12
    VBAX Regular
    Joined
    Oct 2017
    Posts
    20
    Location
    Hello.
    Finally, I found a way to run my code to export an xlBarstacked Chart from MS Access to excel.

    I just did a little modification in my code

    I share here the original line of code:

    Set ch = ws.Shapes.AddChart.chart

    To this new line of code:

    Set ch = ws.Shapes.AddChart(58).chart
    Now I am working in setting min-max values, the last piece of code in my project.

    Once I finish my project, I will work with variable "mychart".

    If I succeed, I hope to analyze which of these two variables (ch and mychart) is more efficient.

    This post related to export chart xlBarStacked from MS Access into MS Excel is Solved.

    Thanks

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •