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

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
>
>
>
>

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" <marinalimeiraa@yahoo.com.br> wrote in message
news:uHpvXC5GGHA.1452@TK2MSFTNGP11.phx.gbl...
>
> 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
>
>
>

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
>>
>>
>>
>>

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

"Tom Ogilvy" <> escreveu na mensagem
news:uanBq45GGHA.3100@tk2msftngp13.phx.gbl...
> 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" <marinalimeiraa@yahoo.com.br> wrote in message
> news:uHpvXC5GGHA.1452@TK2MSFTNGP11.phx.gbl...
>>
>> 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
>>
>>
>>
>
>