Excel recorded macro to VBA to call from a different software

Copper Contributor

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

11 Replies

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

 

Thank you Matt for your help. I almost gave up and though about recording a macro for all 13 sheets I n Unfortunately, the line of code, Current.ChartObjects("myCht" & intLp + 1).Activate had a run-time error, "The item with the specified name wasn't found". Would you please attach the file that worked for you? I guess there are numerous ways to go about looping a macro through all worksheets. At one point, my existing codes worked for referencing the correct data on each sheet, but when I tried my code again, it didn't behave quite as expected (i.e. generating three graphs on the first sheet with the wrong data). Any of your help is greatly appreciated! Junsh

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...

 

ChartImage.png

Sorry to bother you again Matt. I tried running the macro on a different computer to see if there was something wrong with my workstation but ended up with the same error. Wouldn't you incorporate chart naming in the macro? I suppose the error message is prompted because there are no chart name "myCht1", "myCht2", etc. Did you name your chart manually before running the macro? Another question: why did you comment out the first line after the for loop line? 'ActiveSheet.Shapes.Range("myCht" & intLp + 1).Select Thanks! Junsh

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....

 

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

 

 

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.    

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

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

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

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!