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:

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.

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.

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

OBP. Hello.
This is my code baes form a Macro recorded on.

Code:

---

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.

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:

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

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"

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

Why do you have to modify the code?

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

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.

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.

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:

Code:

---

Set ch = ws.Shapes.AddChart.chart

---

To this new line of code:

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