Forum Discussion

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 28, 2018

    Hey Jamiu-

     

    Maybe try using some code like this to loop through your folder.  Please reference code comments to see what each line of code does...

     

    Sub TestLoop()

    'Declarations
    Dim fileName As Variant
    Dim myFilePath As String
    Dim myChart As Chart
    
    myFilePath = "C:\Users\mmickle1\Documents\Excel Ideas & Stuff\"
    fileName = Dir(myFilePath)
    
    While fileName <> ""
      
        Debug.Print fileName 'This example will print the file name to the immediate window
        Set Wkbk = Workbooks.Open(myFilePath & fileName)     'Open Workbook
        Set myChart = ActiveSheet.Shapes.AddChart(xlColumnClustered).Chart 'Add Chart
        myChart.SetSourceData Source:=ActiveSheet.Range("A2:A30") 'Set DataSource Range
        Wkbk.Close SaveChanges:=True  'Close file and save changes
        fileName = Dir 'Set the fileName to the next file
        
    Wend
    
End Sub
    • Jamiu Oyekan Adegbite's avatar
      Jamiu Oyekan Adegbite
      Copper Contributor
      Jun 28, 2018
      Thank you Matt. Could you please describe how to use this code step by step as I am new to using VBA in excel.

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Jun 28, 2018

        Hey Jamiu-

         

        Here's a basic beginners guide that may help:

         

        VBA for Beginners

         

        You'll need to modify the code to fit your needs for it to work.  In addition I didn't understand if the charts already existed in your workbooks or whether you wanted to make new ones.  Depending on your answer, the code could vary.

         

        DISCLAIMER: VBA IS ALMOST ALWAYS IRREVERSIBLE.  SO MAKE SURE YOU HAVE COPIES OF YOUR FILES.

Resources