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

[sabael]

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.

[OBP]

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.

[sabael]

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

[sabael]

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.

[sabael]

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

[OBP]

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"

[sabael]

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

[OBP]

Why do you have to modify the code?

[sabael]

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

[OBP]

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.

[sabael]

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.

[sabael]

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