Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collect data from Excel files
Hi
I have about 60 (incresing everyday) .xls files in a folder called "customers invoices" Format of all the files is the same. How can I exract data from all these files (from this folder)? I need to get data from A6 B14 F34 F36 Any suggestion please. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collect data from Excel files
Change folder in code below to the full directory name you require. The code
will put all the data in the active worksheet in column A - D. I also put the filename in column E. Sub getdata() Folder = "c:\temp\" Set oldsht = ActiveSheet RowCount = 1 FName = Dir(Folder & "*.xls") Do While FName < "" Set newbk = Workbooks.Open(Filename:=Folder & FName) With oldsht .Range("A" & RowCount) = newbk.ActiveSheet.Range("A6") .Range("B" & RowCount) = newbk.ActiveSheet.Range("B14") .Range("C" & RowCount) = newbk.ActiveSheet.Range("F34") .Range("D" & RowCount) = newbk.ActiveSheet.Range("F36") .Range("E" & RowCount) = FName End With newbk.Close savechanges:=False FName = Dir() Loop End Sub "tim" wrote: Hi I have about 60 (incresing everyday) .xls files in a folder called "customers invoices" Format of all the files is the same. How can I exract data from all these files (from this folder)? I need to get data from A6 B14 F34 F36 Any suggestion please. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collect data from Excel files
I forgot to increment the RowCount
Sub getdata() Folder = "c:\temp\" Set oldsht = ActiveSheet RowCount = 1 FName = Dir(Folder & "*.xls") Do While FName < "" Set newbk = Workbooks.Open(Filename:=Folder & FName) With oldsht .Range("A" & RowCount) = newbk.ActiveSheet.Range("A6") .Range("B" & RowCount) = newbk.ActiveSheet.Range("B14") .Range("C" & RowCount) = newbk.ActiveSheet.Range("F34") .Range("D" & RowCount) = newbk.ActiveSheet.Range("F36") .Range("E" & RowCount) = FName End With RowCount = RowCount + 1 newbk.Close savechanges:=False FName = Dir() Loop End Sub "tim" wrote: Hi I have about 60 (incresing everyday) .xls files in a folder called "customers invoices" Format of all the files is the same. How can I exract data from all these files (from this folder)? I need to get data from A6 B14 F34 F36 Any suggestion please. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collect data from Excel files
hi Tim,
you could use an add-in http://xcell05.free.fr/morefunc/english/index.htm the function is indirect.ext http://xcell05.free.fr/morefunc/engl...direct.ext.htm or you need a macro, that in a loop every workbook of a directory -opens, copy/paste data, closes if you want a macro, where/how should the data be pasted? stefan On 22 Mai, 14:32, "tim" wrote: Hi I have about 60 (incresing everyday) .xls files in a folder called "customers invoices" Format of all the files is the same. How can I exract data from all these files (from this folder)? I need to get data from A6 B14 F34 F36 Any suggestion please. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collect data from Excel files
thanks guys.
Joel I am sorry to forgot to mention. The sheet name in the file I want data from is "invoice". Data collected to be pasted in the sheet I am working called "OverView". What changes I ned to make and where to pu the code? regards "tim" wrote in message ... Hi I have about 60 (incresing everyday) .xls files in a folder called "customers invoices" Format of all the files is the same. How can I exract data from all these files (from this folder)? I need to get data from A6 B14 F34 F36 Any suggestion please. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collect data from Excel files
See also
http://www.rondebruin.nl/merge.htm Or http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tim" wrote in message ... Hi I have about 60 (incresing everyday) .xls files in a folder called "customers invoices" Format of all the files is the same. How can I exract data from all these files (from this folder)? I need to get data from A6 B14 F34 F36 Any suggestion please. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Collect data from Excel files
Thanks it was really helpful link.
I would still go for Joel suggested code. Joel I have tried the following code but no luck. any further help would be much appriciated. Sub getdata() Folder = "c:\customers_invoices" Set oldsht = ActiveSheet RowCount = 1 FName = Dir(Folder & "*.xls") Do While FName < "" Set newbk = Workbooks.Open(Filename:=Folder & FName) With oldsht .Range("A" & RowCount) = newbk.ActiveSheet.Range("A6") .Range("B" & RowCount) = newbk.ActiveSheet.Range("B14") .Range("C" & RowCount) = newbk.ActiveSheet.Range("F34") .Range("D" & RowCount) = newbk.ActiveSheet.Range("F36") .Range("E" & RowCount) = FName End With RowCount = RowCount + 1 newbk.Close savechanges:=False FName = Dir() Loop End Sub "Ron de Bruin" wrote in message ... See also http://www.rondebruin.nl/merge.htm Or http://www.rondebruin.nl/summary2.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "tim" wrote in message ... Hi I have about 60 (incresing everyday) .xls files in a folder called "customers invoices" Format of all the files is the same. How can I exract data from all these files (from this folder)? I need to get data from A6 B14 F34 F36 Any suggestion please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
collect data from one excel sheet to another | Excel Discussion (Misc queries) | |||
collect data from one excel sheet to another | Excel Worksheet Functions | |||
How to collect data from every 60th row? | Excel Discussion (Misc queries) | |||
collect data's from different excel files | Excel Worksheet Functions | |||
collect data from some files | Excel Programming |