Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change data of entire column from small case to upper case | Excel Worksheet Functions | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
excel'03 how to convert a column from upper case to proper case | Excel Discussion (Misc queries) | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) | |||
How to use formula auditing to change upper case to Title Case. | Excel Worksheet Functions |