Extract data from different Excelfiles or worsheets

[MarcoNun]

I hope somebody can help me and thank you in advance!!

My problem is that I have around 20 Excelfiles each of them has around 15 Worksheets. Now I would like to extract certain values in order to make calculations and finally to build graphs and diagrams. the value is written next to the descripiton which is common. With copy and paste I get crazy.

How can I automatize it that I can scan Excel files and worksheets for certain values (e.g. total production volume).

Thank you very much!

 

[ravishankar]

Hi
Please give more details of which "Certain values" you want to pull out and what calculations you want to make on them. This will help us to give you the codes faster.
Ravi

 

[MarcoNun]

Thank you for the quick reply.

Lets say I have from several production plants excel files that contaîn data like Production, Capacity, Inventory, Yield ....

Instead of calculation let's just say that I would like to list all the excel files and then extract from all files for example the production figues witout opening 50 files and copy and paste them.

Where should I start? Visual Basic tutorial?
VLOOKUP I can only use when I have already a excel table,no?

Thank you,
Marco

 

[Norie]

Marco

You really need to give us some more specific information.

Where will Production, Capacity, Inventory, Yield... be located?

 

[stanleydgromjr]

MarcoNun,

How often will you be consolidating the information?

If it is only once, then do it manually. If it is to be done on a regular schedule, then a macro will be needed.

For a regular schedule:
What is the full path to the folder/directory where the 'around 20 files' reside?

What is the range of the information in each/all of the worksheets in each workbook?

What is the name of the workbook and sheetname where the information is to be copied to?

Have a great day,
Stan

 

[MarcoNun]

I'm Sorry!! Let's simplify the problem:

I have in Excel file Alpha.xls and in the Worksheet "Operational Data"
A1: Production
A2: 200

I have in Excel file Beta.xls and in the Worksheet "Operational Data"
A1: Production
A2: 500

How can I create a table (without copy and paste!) with the following information:

ColumnA ColumnB ColumnC
Alpha Production 200
Beta Production 500

Thank you and I appriciate the help!

 

[MarcoNun]

Thank you very much ,Stan!

That's excatly the problem we talking about a lot of data and in addition they keep changing the data and with copy and paste you go crazy.
So I need a makro?

All files will be send to me so I can give myself an adequate path or file name. At least the worksheet description is everywhere the same.

I know probably it is not so simple as it looks like, but should I just start searching for Makro tutorials??

Thank you

 

[ravishankar]

HI
Paste the following codes in the macro window ( Alt F11)

Sub hhh()
Cells(1, 1).Select
  f = Dir("C:\" & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
n = n + 1
Loop
x = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To x
h = Cells(a, 1)
Cells(a, 2) = "='C:\[" & h & "]sheet1'!A1"
Cells(a, 3) = "='C:\[" & h & "]sheet1'!A2"
Next a
End Sub

Change C:\ to the file path where your 20+ folders are located. This macro when run will list all excel files in that folder and pulls out A1 & A2 values from sheet1 and lists them. To list all 15 sheet's contents we need to modify the codes.
Ravi

 

[MarcoNun]

Thank you so much!
I will try it out right now!

Thank you!

 

[stanleydgromjr]

Part 1

MarcoNun,

Please TEST this FIRST in a COPY of your workbook.

Before the macro:

Summary for MarcoNun.xls
	A	B	C	D	E	F	G
1	FileName	Category	Volume		TestDataFilesContain
2					FileName	Category	Volume
3
4					Alpha	OperationalData!
5					A1	ProductionA
6					A2	100
7
8					Beta	OperationalData!
9					A1	ProductionB
10					A2	200
11
12					Charlie	OperationalData!
13					A1	ProductionA
14					A2	300
15
16					Dog	OperationalData!
17					A1	ProductionA
18					A2	400
19
20					Echo	OperationalData!
21					A1	ProductionB
22					A2	500
23
24					ExpectedResults
25					Alpha	ProductionA	100
26					Beta	ProductionB	200
27					Charlie	ProductionA	300
28					Dog	ProductionA	400
29					Echo	ProductionB	500
Summary