Jun 27 2018
11:59 AM
- last edited on
Jul 25 2018
10:05 AM
by
TechCommunityAP
Jun 27 2018
11:59 AM
- last edited on
Jul 25 2018
10:05 AM
by
TechCommunityAP
Hello,
I have no functional knowledge of VBA but needs to call a VBA from a different software (namely SAS) to create chart on multiple excel worksheets with specific formats.
I've recorded a macro for one sheet and used the following to repeat for all sheets included in the excel file but my x-axis label is off for the first two sheets and data labels for the last two sheets are not shown. I don't know how to reference the current sheet names properly.
Sub WorksheetLoop2()
' Declare Current as a worksheet object variable.
Dim Current As Worksheet
' Loop through all of the worksheets in the active workbook.
For Each Current In Worksheets
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("B5:M5")
''ActiveChart.SeriesCollection(1).XValues = _
'' "='Table 1 - Cross-tabular summary'!$B$1:$M$2"
ActiveChart.SeriesCollection(1).XValues = "=Current!B$1:$M$2"
ActiveChart.SeriesCollection(1).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
ActiveChart.SeriesCollection(1).DataLabels.Select
ActiveChart.SeriesCollection(1).Select
'' (JS) change "Chart 1" to the numeric location of the chart rather than the name
'ActiveSheet.Shapes(I).IncrementLeft -434.25
'ActiveSheet.Shapes(I).IncrementTop -69.75
MsgBox Current.Name
Next
End Sub
Jun 28 2018 07:27 AM - edited Jun 28 2018 04:46 PM
Hello Junsh-
Please try the below code. See attached workbook for reference:
Note: I have named the charts in order... myCht1, myCht2, myCht3 .....
Sub WorksheetLoop2() ' Declare Current as a worksheet object variable. Dim Current As Worksheet Dim intLp As Integer ' Loop through all of the worksheets in the active workbook. For Each Current In Worksheets 'ActiveSheet.Shapes.Range("myCht" & intLp + 1).Select Current.ChartObjects("myCht" & intLp + 1).Activate With ActiveChart .ChartType = xlColumnClustered .SetSourceData Source:=Range("B5:M5") .SeriesCollection(1).XValues = "=Current!B$1:$M$2" .SeriesCollection(1).Select .PlotArea.Select .SeriesCollection(1).Select .SeriesCollection(1).ApplyDataLabels .SeriesCollection(1).DataLabels.Select .SeriesCollection(1).Select End With intLp = intLp + 1 'MsgBox Current.Name Next End Sub
Jun 28 2018 03:47 PM
Jun 28 2018 04:48 PM
Sorry I must have forgot to attach the workbook. See previous post. You have to rename the charts for it to work properly. Select the chart then change the name in the upper left hand corner. They need to be in the order the sheets are in...
Jun 29 2018 11:34 AM
Jun 29 2018 02:04 PM
I did name them manually before hand. I wasn't sure if you had more than one chart on each worksheet. So I wanted to make sure I could name them based on an easy convention. This also prevents the need to cycle through all of the shapes in the workbook looking for the chart.
The line I commented out was done so because it serves no purpose. I was fiddling around testing how to work with charts, because it's been a while and during the testing I determined this wasn't necessary.
I could incorporate naming the chart in the file you are correct. I just wanted the most direct way to access the chart objects without having to cycle thorough any others....
Jul 03 2018 03:00 PM
Thanks for your help again, Matt.
I understand that it may not be ideal to create multiple objects cycling through within the workbook.
Before running the macro, I tried to manually insert an empty bar plot within each sheet (without selecting any data source) and renamed the default chart name to "myChtX" with corresponding sheet number, the macro worked well. However, as I am trying to automate the whole process by executing the excel macro from another program, I need to incorporate the process of referencing the correct chart name within each active sheet. Would you kindly show how to incorporate it if it's not too much trouble for you?
Thank you very much!
Junsh
Jul 03 2018 03:29 PM
By the way, I greatly appreciate all your help and time. It's been quite challenging for someone like me without much knowledge about EXCEL macro and its intricacies.
Jul 06 2018 06:11 AM - edited Jul 06 2018 06:12 AM
Junsh-
You're quite welcome. Why don't you try using some code like this that will allow you to specify which chart is on which sheet based on a few arrays. The arrays correspond to the sheet names and the chart names. Please see attached .xlsm example file for reference. :
Sub WorksheetLoop2() 'Declare variables Dim intLp As Integer Dim sht As Worksheet Dim shtArr As Variant Dim chtArr As Variant 'Build arrays that correspond to each other 'i.e. Sheet1 has myCht1 on it....Sheet2 has myCht2 on it shtArr = Array("Sheet1", "Sheet2", "Sheet3") chtArr = Array("myCht1", "myCht2", "myCht3") 'Loop through all of the worksheets in the above array 'Please not that array indexes start at 0 not 1 'This means that Sheet1 and myCht1 will be found using a 0... For intLp = 0 To 2 Sheets(shtArr(intLp)).Activate Set sht = ActiveSheet sht.ChartObjects(chtArr(intLp)).Activate With ActiveChart .ChartType = xlColumnClustered .SetSourceData Source:=sht.Range("B5:M5") .SeriesCollection(1).XValues = sht.Range("B1:M2") .SeriesCollection(1).Select .PlotArea.Select .SeriesCollection(1).Select .SeriesCollection(1).ApplyDataLabels .SeriesCollection(1).DataLabels.Select .SeriesCollection(1).Select End With Next End Sub
Jul 06 2018 01:52 PM
Thanks Matt again for your help. I tried your macro after removing the charts from the sheet and got the error message:
The item with the specified name wasn't found.
As I wasn't sure if the error was due to missing chart objects, I've added an empty chart, renamed it for all three sheets but still got the same error.
Does the line "sht.ChartObjects(chtArr(intLp)).Activate" add an empty object or activate the chart? Hmm, I don't know why it doesn't produce the results as intended. Do we need to "add" chart objects before activating them? If the same logic of activating sheet can be applied to activating chart objects, I believe we should start with existing chart objects before activating them (because the three sheets named properly exist before running the macro). Would you like to shed some light?
Thanks!
Junsh
Jul 06 2018 02:55 PM
Hi Matt,
I think the following worked! Thanks so much for your suggestion to include the arrays!
Sub WorksheetLoop2()
'Declare variables
Dim intLp As Integer
Dim sht As Worksheet
Dim shtArr As Variant
Dim chtArr As Variant
'Build arrays that correspond to each other
'i.e. Sheet1 has myCht1 on it....Sheet2 has myCht2 on it
shtArr = Array("mySheet1", "mySheet2", "mySheet3", "mySheet4", "mySheet5", "mySheet6", "mySheet7", "mySheet8", "mySheet9", "mySheet10", "mySheet11", "mySheet12", "mySheet13")
chtArr = Array("myCht1", "myCht2", "myCht3", "myCht4", "myCht5", "myCht6", "myCht7", "myCht8", "myCht9", "myCht10", "myCht11", "myCht12", "myCht13")
'Loop through all of the worksheets in the above array
'Please not that array indexes start at 0 not 1
'This means that Sheet1 and myCht1 will be found using a 0...
'Added by Jungwon
For intLp = 0 To 12
Sheets(shtArr(intLp)).Activate
Set sht = ActiveSheet
sht.Shapes.AddChart.Select 'Added by JS
sht.Shapes(1).Name = chtArr(intLp) 'Added by JS
sht.ChartObjects(chtArr(intLp)).Activate
With ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=sht.Range("B5:M5")
.SeriesCollection(1).XValues = sht.Range("B1:M2")
.SeriesCollection(1).Select
.PlotArea.Select
.SeriesCollection(1).Select
.SeriesCollection(1).ApplyDataLabels
.SeriesCollection(1).DataLabels.Select
.SeriesCollection(1).Select
End With
Next
End Sub
Jul 06 2018 03:19 PM
Glad you got it working. As you can see the code I supplied required the charts to already be created and named. The alterations you made add the chart and then name it. Way to go! Glad you were able to figure it out. Hopefully, your trial and error helped to clear up what the code was doing a bit!
Cheers!