Forum Discussion

junsh.grace's avatar
junsh.grace
Copper Contributor
Jun 27, 2018

Excel recorded macro to VBA to call from a different software

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...
Macros & VBA
Need Help
junsh.grace's avatar
junsh.grace
Copper Contributor
Jun 28, 2018
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
Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Jun 28, 2018

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

 

  • junsh.grace's avatar
    junsh.grace
    Copper Contributor
    Jun 29, 2018
    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
    • Matt Mickle's avatar
      Matt Mickle
      Bronze Contributor
      Jun 29, 2018

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

       

      • junsh.grace's avatar
        junsh.grace
        Copper Contributor
        Jul 03, 2018

        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.    

Resources