Pull Data from Multiple Excel Files into a Summary Excel File to Create Graphs

[ThomasE]

I have a set of Excel sheets that are outputs from different tests. There are 9 raw data files, and they are sequenced by when the test was conducted.

001_X_PreSine.xlsx
002_X_Random.xlsx
003_X_PostSine.xlsx
004_Y_PreSine.xlsx
005_Y_Random.xlsx
006_Y_PostSine.xlsx
007_Z_PreSine.xlsx
008_Z_Random.xlsx
009_Z_PostSine.xlsx

X, Y, and Z in the file names above will always be grouped together, but the letters could be in any order (X, Y, Z / X, Z, Y / Y, Z, X / etc.).

I want to take the data from the PreSine and PostSine files, which always starts on line 25 and ends on line 2024 of the first sheet and graph the data showing PreSine and PostSine for the same axis (X, Y, Z) in the same graph.

For the graphs, the X-values come from column B, and Y-values come from column G, I, K, and M. Data labels would be "Control", "X-Response", "Y-Response", and "Z-Response" with a prefix of either "Pre Sine" or "Post Sine" as appropriate.

Is it possible to program this by having all of the raw data files and summary file with graphs in the same folder?

 

[ThomasE]

I have 2 modules - one with the primary code and a second with the operate code. When I run debug from the operate code, it does step into the main code and then it appears to find the first file (a PreSine), then find the second file (a Random), then opens the that single PreSine file (001_PreSine_X) and then has a message box with "LoadChartDataError". If I am following the code correctly, the exact file name will not matter as long as it does not have the word "Random" in it.

I did try removing the Random data files from the folder, but this did not help either. It still opened a single file.

I am happy to share a whole set of files with you and strip out customer data, but I am not sure the best way to get them to you.

 

[NdNoviceHlp]

The problem may be that the files to be imported do not have a sheet named "Customers". So as long as there is only 1 sheet per data file, this will work no matter what sheet name replaces "Customers". HTH. Dave
Replace...

Set DataWs = WbSource.Worksheets("Customers")

with...

Set DataWs = WbSource.Worksheets(1)