ideas to case
 

Define Database. Are you talking about uploading the Excel spreadsheets to an
Access (or some other) database or were you wanting to create on big Excel
file. My preference for what you are trying to do is the Access Database
assuming that you are dealing with a relatively large number of records (
65,536). Once you have created the database (Access or Excel) then you can
just connect a pivot table to the data any your report should be very simple
from there.

This is a fairly big project. Are you familiar with
Databases such as Access?
ADODB Recordsets?
Pivot Tables?
Addins?

--
HTH...

Jim Thomlinson


"Marina Limeira" wrote:


I have 120 equal format files about hospital produces in cities.

example: group files.xls are territories files name

City item peoples
A item01 100

B item02 183
item03 432
Total
C item04 34


Are you send oppinion about I imagine create a big database with import from
120 files .xls
to analysing...

territory | city | item | people
1 A 01 100
1 B 02 183
1 B 03 432
....
3 A 01 343
3 A 02 433

Question, also example from VBA to import from several files.xls to big
centralize file database sheet ?

thanks for helping me

Marina

ideas to case
 

I have 120 equal format files about hospital produces in cities.

example: group files.xls are territories files name

City item peoples
A item01 100

B item02 183
item03 432
Total
C item04 34


Are you send oppinion about I imagine create a big database with import from
120 files .xls
to analysing...

territory | city | item | people
1 A 01 100
1 B 02 183
1 B 03 432
....
3 A 01 343
3 A 02 433

Question, also example from VBA to import from several files.xls to big
centralize file database sheet ?

thanks for helping me

Marina

ideas to case
 

Sub GroupData()
Dim sPath as String, sName as String
Dim bk as Workbook, i as Long
Dim sh as Worksheet, lastrow as Long
Dim rng as Range, rng1 as Range, rng2 as Range

sPath = "C:\MyFiles\"
sname = dir(sPath & "*.xls")
do while sName < ""
set bk = Workbooks.Open(sPath & sName)
set sh = bk.worksheets(1)
set lastrow = sh.cells(rows.count,1).End(xlup).row
for i = lastrow to 2 step -1
if instr(1,sh.cells(i,1),"total",vbtextcompare) then
sh.rows(i).Delete
else
if isempty(sh.cells(i,1)) then
set rng = sh.cells(i,1).End(xlup)
sh.cells(i,1).Value = rng
end if
end if
Next
set rng1 = thisworkbooks.worksheets(1) _
.Cells(rows.count,2).end(xlup)(2)
sh.Range("A1").currentRegion.Copy Destination:=rng1
bk.close Savechanges:=False
set rng2 = rng1.Parent.Range(rng1,rng1.End(xldown))
rng2.offset(0,-1).Value = sName
sName = dir()
Loop
rng2.Parent.Range("A1:D1").Value = _
Array("territory","city","item","people")
End sub

Should get you started. I put in the filename for the territory
Assumes you data won't exceed the number of rows in one sheet and that the
code is hosted in the workbook where you want to consolidate the data (on
the first sheet in the tab order)

--
Regards,
Tom Ogilvy


"Marina Limeira" wrote in message
...

I have 120 equal format files about hospital produces in cities.

example: group files.xls are territories files name

City item peoples
A item01 100

B item02 183
item03 432
Total
C item04 34


Are you send oppinion about I imagine create a big database with import
from
120 files .xls
to analysing...

territory | city | item | people
1 A 01 100
1 B 02 183
1 B 03 432
...
3 A 01 343
3 A 02 433

Question, also example from VBA to import from several files.xls to big
centralize file database sheet ?

thanks for helping me

Marina

ideas to case
 

I want to use Pivot Tables in Excel solutions only....
The total records is < 65000..
also exemple this case similar?

Marina


Define Database. Are you talking about uploading the Excel spreadsheets to
an
Access (or some other) database or were you wanting to create on big Excel
file. My preference for what you are trying to do is the Access Database
assuming that you are dealing with a relatively large number of records (
65,536). Once you have created the database (Access or Excel) then you can
just connect a pivot table to the data any your report should be very
simple
from there.

This is a fairly big project. Are you familiar with
Databases such as Access?
ADODB Recordsets?
Pivot Tables?
Addins?

--
HTH...

Jim Thomlinson


"Marina Limeira" wrote:


I have 120 equal format files about hospital produces in cities.

example: group files.xls are territories files name

City item peoples
A item01 100

B item02 183
item03 432
Total
C item04 34


Are you send oppinion about I imagine create a big database with import
from
120 files .xls
to analysing...

territory | city | item | people
1 A 01 100
1 B 02 183
1 B 03 432
....
3 A 01 343
3 A 02 433

Question, also example from VBA to import from several files.xls to big
centralize file database sheet ?

thanks for helping me

Marina

ideas to case
 

Thanks tom
If exist row in blank? how only copy value data?
and all worksheets?
thanks again

"Tom Ogilvy" < escreveu na mensagem
...
Sub GroupData()
Dim sPath as String, sName as String
Dim bk as Workbook, i as Long
Dim sh as Worksheet, lastrow as Long
Dim rng as Range, rng1 as Range, rng2 as Range

sPath = "C:\MyFiles\"
sname = dir(sPath & "*.xls")
do while sName < ""
set bk = Workbooks.Open(sPath & sName)
set sh = bk.worksheets(1)
set lastrow = sh.cells(rows.count,1).End(xlup).row
for i = lastrow to 2 step -1
if instr(1,sh.cells(i,1),"total",vbtextcompare) then
sh.rows(i).Delete
else
if isempty(sh.cells(i,1)) then
set rng = sh.cells(i,1).End(xlup)
sh.cells(i,1).Value = rng
end if
end if
Next
set rng1 = thisworkbooks.worksheets(1) _
.Cells(rows.count,2).end(xlup)(2)
sh.Range("A1").currentRegion.Copy Destination:=rng1
bk.close Savechanges:=False
set rng2 = rng1.Parent.Range(rng1,rng1.End(xldown))
rng2.offset(0,-1).Value = sName
sName = dir()
Loop
rng2.Parent.Range("A1:D1").Value = _
Array("territory","city","item","people")
End sub

Should get you started. I put in the filename for the territory
Assumes you data won't exceed the number of rows in one sheet and that the
code is hosted in the workbook where you want to consolidate the data (on
the first sheet in the tab order)

--
Regards,
Tom Ogilvy


"Marina Limeira" wrote in message
...

I have 120 equal format files about hospital produces in cities.

example: group files.xls are territories files name

City item peoples
A item01 100

B item02 183
item03 432
Total
C item04 34


Are you send oppinion about I imagine create a big database with import
from
120 files .xls
to analysing...

territory | city | item | people
1 A 01 100
1 B 02 183
1 B 03 432
...
3 A 01 343
3 A 02 433

Question, also example from VBA to import from several files.xls to big
centralize file database sheet ?

thanks for helping me

Marina