Forum Discussion
Plotting Excel data from several workbooks together automatically.
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 exce...
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
- Thank you Matt. Could you please describe how to use this code step by step as I am new to using VBA in excel.
Hey Jamiu-
Here's a basic beginners guide that may help:
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.
- 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.
