Plotting Excel data from several workbooks together automatically.

[Jamiu Oyekan Adegbite]

Hello Please, I have about 60 excel workbooks in a folder. With the same data structure. I need to create another excel workbook that automatically plot "a certain column data" in each of the 60 excel workbooks without doing it one by one.

[#2]

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

[#3]

Thank you Matt. Could you please describe how to use this code step by step as I am new to using VBA in excel.

[#4]

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.

[#5]

Thank you Matt. But seems I am still battling with it.

The challenge is:
1: I have like 60 excel files in a folder
2: I created another excel file specifically to plot all the data in each of the excel files inside the folder together.

[#6]

Thank you Matt. But seems I am still battling with it.

The challenge is:
1: I have like 60 excel files in a folder
2: I created another excel file specifically to plot all the data in each of the excel files inside the folder together.
3: What I need to do is to create a vba code for the chart that pulls specific data from each of the 60 excel files in the folder and plot them together automatically.
Please kindly help with this. Thanks.

[#7]

Can you tell me where the data is in each of the 60 workbooks?  It sounds like you need to iterate through the files and pull the data out of them into a master file and then plot them all in a single graph based on the consolidated data in the master file....

It may be easier to use Power Query than VBA.  It just depends on the scenario:

Consolidate Multiple Workbooks with Power Query

Retrieve Values from many workbooks

[#8]

Thank you Matt. The data is in Column AX and AY - From Row 4 to Row 45 for each of the 60 excel files. I want to make Column AX the y-axis and Column AY the x-axis and plot them.

Yes, it seems that's what i want to do.