| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy several range from all files in folder into several worksheets
Hello
I searched a lot into the newsgroup, but can't find it. I want to copy a range (or some rows) from all files in a folder. The copies ranges must be stored in different worksheets (prefered name of file). I hope the questions is clear. Can someone help? Regards, Adri |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
Hi Adri
Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook sFolder = "C:\MyTest" If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=file.Path this.Worksheets.Add.Name = "File" & cnt With ActiveWorkbook .Worksheets(1).Range("A1:C100").Copy _ Destination:=this.Worksheets("File" & cnt).Range("A1") .Close End With cnt = cnt + 1 End If Next file End If ' sFolder < "" End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Adri" wrote in message ... Hello I searched a lot into the newsgroup, but can't find it. I want to copy a range (or some rows) from all files in a folder. The copies ranges must be stored in different worksheets (prefered name of file). I hope the questions is clear. Can someone help? Regards, Adri |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
Hi Bob,
Thanks for your reactions, but I can get the code to work. In folder c:\mytest I put two testfiles (test1.xls and test2.xls). In c:\ I put a file called test.xls and put the code in a module. When I run the macro nothing happens, also no erros. The data (range A1:A100) from the testfiles in C:\mytest should be in the sheets test1 en test2. What am I doing wrong. Adri Bob Phillips wrote: | Hi Adri | | Dim FSO As Object | | Sub ProcessFiles() | Dim i As Long | Dim sFolder As String | Dim fldr As Object | Dim Folder As Object | Dim file As Object | Dim Files As Object | Dim this As Workbook | Dim cnt As Long | | Set FSO = CreateObject("Scripting.FileSystemObject") | | Set this = ActiveWorkbook | sFolder = "C:\MyTest" | If sFolder < "" Then | Set Folder = FSO.GetFolder(sFolder) | | Set Files = Folder.Files | cnt = 1 | For Each file In Files | If file.Type = "Microsoft Excel Worksheet" Then | Workbooks.Open Filename:=file.Path | this.Worksheets.Add.Name = "File" & cnt | With ActiveWorkbook | .Worksheets(1).Range("A1:C100").Copy _ | Destination:=this.Worksheets("File" & | cnt).Range("A1") | .Close | End With | cnt = cnt + 1 | End If | Next file | | End If ' sFolder < "" | | End Sub | | | "Adri" wrote in message | ... || Hello || I searched a lot into the newsgroup, but can't find it. || I want to copy a range (or some rows) from all files in a folder. || The copies ranges must be stored in different worksheets (prefered || name of file). || I hope the questions is clear. Can someone help? || Regards, Adri |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
Presumably you have modified the code? What does it look like now? And what
Excel version do you have? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Adri" wrote in message ... Hi Bob, Thanks for your reactions, but I can get the code to work. In folder c:\mytest I put two testfiles (test1.xls and test2.xls). In c:\ I put a file called test.xls and put the code in a module. When I run the macro nothing happens, also no erros. The data (range A1:A100) from the testfiles in C:\mytest should be in the sheets test1 en test2. What am I doing wrong. Adri Bob Phillips wrote: | Hi Adri | | Dim FSO As Object | | Sub ProcessFiles() | Dim i As Long | Dim sFolder As String | Dim fldr As Object | Dim Folder As Object | Dim file As Object | Dim Files As Object | Dim this As Workbook | Dim cnt As Long | | Set FSO = CreateObject("Scripting.FileSystemObject") | | Set this = ActiveWorkbook | sFolder = "C:\MyTest" | If sFolder < "" Then | Set Folder = FSO.GetFolder(sFolder) | | Set Files = Folder.Files | cnt = 1 | For Each file In Files | If file.Type = "Microsoft Excel Worksheet" Then | Workbooks.Open Filename:=file.Path | this.Worksheets.Add.Name = "File" & cnt | With ActiveWorkbook | .Worksheets(1).Range("A1:C100").Copy _ | Destination:=this.Worksheets("File" & | cnt).Range("A1") | .Close | End With | cnt = cnt + 1 | End If | Next file | | End If ' sFolder < "" | | End Sub | | | "Adri" wrote in message | ... || Hello || I searched a lot into the newsgroup, but can't find it. || I want to copy a range (or some rows) from all files in a folder. || The copies ranges must be stored in different worksheets (prefered || name of file). || I hope the questions is clear. Can someone help? || Regards, Adri |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
Bob Phillips wrote:
| Presumably you have modified the code? What does it look like now? | And what Excel version do you have? Hi Bob, code in module 1 of c:\text.xls is (didn't modify it) I'm working with Excel2000. After Dim FSO as Object follws a line, is that correct? Option Explicit Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook sFolder = "C:\MyTest" If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=file.Path this.Worksheets.Add.Name = "File" & cnt With ActiveWorkbook .Worksheets(1).Range("A1:C100").Copy Destination:=this.Worksheets("File" & cnt).Range("A1") .Close End With cnt = cnt + 1 End If Next file End If ' sFolder < "" End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
Adri,
The line after Dim FSO is just for readability, it does nolthing and affects nothing. I wonder if it is just wrap-around. Try this version Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook sFolder = "C:\MyTest" If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=file.Path this.Worksheets.Add.Name = "File" & cnt With ActiveWorkbook .Worksheets(1).Range("A1:C100").Copy _ Destination:=this.Worksheets("File" & cnt).Range("A1") .Close End With cnt = cnt + 1 End If Next file End If ' sFolder < "" End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Adri" wrote in message ... Bob Phillips wrote: | Presumably you have modified the code? What does it look like now? | And what Excel version do you have? Hi Bob, code in module 1 of c:\text.xls is (didn't modify it) I'm working with Excel2000. After Dim FSO as Object follws a line, is that correct? Option Explicit Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook sFolder = "C:\MyTest" If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Workbooks.Open Filename:=file.Path this.Worksheets.Add.Name = "File" & cnt With ActiveWorkbook .Worksheets(1).Range("A1:C100").Copy Destination:=this.Worksheets("File" & cnt).Range("A1") .Close End With cnt = cnt + 1 End If Next file End If ' sFolder < "" End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
Bob, the last version does not work either.
Bob Phillips wrote: | Adri, | | The line after Dim FSO is just for readability, it does nolthing and | affects nothing. I wonder if it is just wrap-around. | | Try this version | | Dim FSO As Object | | Sub ProcessFiles() | Dim i As Long | Dim sFolder As String | Dim fldr As Object | Dim Folder As Object | Dim file As Object | Dim Files As Object | Dim this As Workbook | Dim cnt As Long | | Set FSO = CreateObject("Scripting.FileSystemObject") | | Set this = ActiveWorkbook | sFolder = "C:\MyTest" | If sFolder < "" Then | Set Folder = FSO.GetFolder(sFolder) | | Set Files = Folder.Files | cnt = 1 | For Each file In Files | If file.Type = "Microsoft Excel Worksheet" Then | Workbooks.Open Filename:=file.Path | this.Worksheets.Add.Name = "File" & cnt | With ActiveWorkbook | .Worksheets(1).Range("A1:C100").Copy _ | Destination:=this.Worksheets("File" & cnt).Range("A1") | .Close | End With | cnt = cnt + 1 | End If | Next file | | End If ' sFolder < "" | | End Sub | | | | "Adri" wrote in message | ... || Bob Phillips wrote: ||| Presumably you have modified the code? What does it look like now? ||| And what Excel version do you have? || || Hi Bob, code in module 1 of c:\text.xls is (didn't modify it) || I'm working with Excel2000. || After Dim FSO as Object follws a line, is that correct? || || Option Explicit || || Dim FSO As Object || || Sub ProcessFiles() || Dim i As Long || Dim sFolder As String || Dim fldr As Object || Dim Folder As Object || Dim file As Object || Dim Files As Object || Dim this As Workbook || Dim cnt As Long || || Set FSO = CreateObject("Scripting.FileSystemObject") || || Set this = ActiveWorkbook || sFolder = "C:\MyTest" || If sFolder < "" Then || Set Folder = FSO.GetFolder(sFolder) || || Set Files = Folder.Files || cnt = 1 || For Each file In Files || If file.Type = "Microsoft Excel Worksheet" Then || Workbooks.Open Filename:=file.Path || this.Worksheets.Add.Name = "File" & cnt || With ActiveWorkbook || .Worksheets(1).Range("A1:C100").Copy || Destination:=this.Worksheets("File" & cnt).Range("A1") || .Close || End With || cnt = cnt + 1 || End If || Next file || || End If ' sFolder < "" || || End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
I recreated the environment you describe and ran the code. It ran fine for
me. -- Regards, Tom Ogilvy "Adri" wrote in message ... Bob, the last version does not work either. Bob Phillips wrote: | Adri, | | The line after Dim FSO is just for readability, it does nolthing and | affects nothing. I wonder if it is just wrap-around. | | Try this version | | Dim FSO As Object | | Sub ProcessFiles() | Dim i As Long | Dim sFolder As String | Dim fldr As Object | Dim Folder As Object | Dim file As Object | Dim Files As Object | Dim this As Workbook | Dim cnt As Long | | Set FSO = CreateObject("Scripting.FileSystemObject") | | Set this = ActiveWorkbook | sFolder = "C:\MyTest" | If sFolder < "" Then | Set Folder = FSO.GetFolder(sFolder) | | Set Files = Folder.Files | cnt = 1 | For Each file In Files | If file.Type = "Microsoft Excel Worksheet" Then | Workbooks.Open Filename:=file.Path | this.Worksheets.Add.Name = "File" & cnt | With ActiveWorkbook | .Worksheets(1).Range("A1:C100").Copy _ | Destination:=this.Worksheets("File" & cnt).Range("A1") | .Close | End With | cnt = cnt + 1 | End If | Next file | | End If ' sFolder < "" | | End Sub | | | | "Adri" wrote in message | ... || Bob Phillips wrote: ||| Presumably you have modified the code? What does it look like now? ||| And what Excel version do you have? || || Hi Bob, code in module 1 of c:\text.xls is (didn't modify it) || I'm working with Excel2000. || After Dim FSO as Object follws a line, is that correct? || || Option Explicit || || Dim FSO As Object || || Sub ProcessFiles() || Dim i As Long || Dim sFolder As String || Dim fldr As Object || Dim Folder As Object || Dim file As Object || Dim Files As Object || Dim this As Workbook || Dim cnt As Long || || Set FSO = CreateObject("Scripting.FileSystemObject") || || Set this = ActiveWorkbook || sFolder = "C:\MyTest" || If sFolder < "" Then || Set Folder = FSO.GetFolder(sFolder) || || Set Files = Folder.Files || cnt = 1 || For Each file In Files || If file.Type = "Microsoft Excel Worksheet" Then || Workbooks.Open Filename:=file.Path || this.Worksheets.Add.Name = "File" & cnt || With ActiveWorkbook || .Worksheets(1).Range("A1:C100").Copy || Destination:=this.Worksheets("File" & cnt).Range("A1") || .Close || End With || cnt = cnt + 1 || End If || Next file || || End If ' sFolder < "" || || End Sub |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
I try and try, but nothing really happens. I have a Excel2000 Dutch version.
Any solutions?? Adri Tom Ogilvy wrote: | I recreated the environment you describe and ran the code. It ran | fine for me. | | | "Adri" wrote in message | ... || Bob, the last version does not work either. || || Bob Phillips wrote: ||| Adri, ||| ||| The line after Dim FSO is just for readability, it does nolthing and ||| affects nothing. I wonder if it is just wrap-around. ||| ||| Try this version ||| ||| Dim FSO As Object ||| ||| Sub ProcessFiles() ||| Dim i As Long ||| Dim sFolder As String ||| Dim fldr As Object ||| Dim Folder As Object ||| Dim file As Object ||| Dim Files As Object ||| Dim this As Workbook ||| Dim cnt As Long ||| ||| Set FSO = CreateObject("Scripting.FileSystemObject") ||| ||| Set this = ActiveWorkbook ||| sFolder = "C:\MyTest" ||| If sFolder < "" Then ||| Set Folder = FSO.GetFolder(sFolder) ||| ||| Set Files = Folder.Files ||| cnt = 1 ||| For Each file In Files ||| If file.Type = "Microsoft Excel Worksheet" Then ||| Workbooks.Open Filename:=file.Path ||| this.Worksheets.Add.Name = "File" & cnt ||| With ActiveWorkbook ||| .Worksheets(1).Range("A1:C100").Copy _ ||| Destination:=this.Worksheets("File" & cnt).Range("A1") ||| .Close ||| End With ||| cnt = cnt + 1 ||| End If ||| Next file ||| ||| End If ' sFolder < "" ||| ||| End Sub ||| ||| ||| ||| "Adri" wrote in message ||| ... |||| Bob Phillips wrote: ||||| Presumably you have modified the code? What does it look like now? ||||| And what Excel version do you have? |||| |||| Hi Bob, code in module 1 of c:\text.xls is (didn't modify it) |||| I'm working with Excel2000. |||| After Dim FSO as Object follws a line, is that correct? |||| |||| Option Explicit |||| |||| Dim FSO As Object |||| |||| Sub ProcessFiles() |||| Dim i As Long |||| Dim sFolder As String |||| Dim fldr As Object |||| Dim Folder As Object |||| Dim file As Object |||| Dim Files As Object |||| Dim this As Workbook |||| Dim cnt As Long |||| |||| Set FSO = CreateObject("Scripting.FileSystemObject") |||| |||| Set this = ActiveWorkbook |||| sFolder = "C:\MyTest" |||| If sFolder < "" Then |||| Set Folder = FSO.GetFolder(sFolder) |||| |||| Set Files = Folder.Files |||| cnt = 1 |||| For Each file In Files |||| If file.Type = "Microsoft Excel Worksheet" Then |||| Workbooks.Open Filename:=file.Path |||| this.Worksheets.Add.Name = "File" & cnt |||| With ActiveWorkbook |||| .Worksheets(1).Range("A1:C100").Copy |||| Destination:=this.Worksheets("File" & cnt).Range("A1") |||| .Close |||| End With |||| cnt = cnt + 1 |||| End If |||| Next file |||| |||| End If ' sFolder < "" |||| |||| End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
Adri,
with the addition of 'dim fso as object' is runs fine for me too. see my elaborate answer to your multipost in nl.office.excel keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Tom Ogilvy" wrote: I recreated the environment you describe and ran the code. It ran fine for me. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
Can't fix what isn't broken.
-- Regards, Tom Ogilvy "Adri" wrote in message ... I try and try, but nothing really happens. I have a Excel2000 Dutch version. Any solutions?? Adri Tom Ogilvy wrote: | I recreated the environment you describe and ran the code. It ran | fine for me. | | | "Adri" wrote in message | ... || Bob, the last version does not work either. || || Bob Phillips wrote: ||| Adri, ||| ||| The line after Dim FSO is just for readability, it does nolthing and ||| affects nothing. I wonder if it is just wrap-around. ||| ||| Try this version ||| ||| Dim FSO As Object ||| ||| Sub ProcessFiles() ||| Dim i As Long ||| Dim sFolder As String ||| Dim fldr As Object ||| Dim Folder As Object ||| Dim file As Object ||| Dim Files As Object ||| Dim this As Workbook ||| Dim cnt As Long ||| ||| Set FSO = CreateObject("Scripting.FileSystemObject") ||| ||| Set this = ActiveWorkbook ||| sFolder = "C:\MyTest" ||| If sFolder < "" Then ||| Set Folder = FSO.GetFolder(sFolder) ||| ||| Set Files = Folder.Files ||| cnt = 1 ||| For Each file In Files ||| If file.Type = "Microsoft Excel Worksheet" Then ||| Workbooks.Open Filename:=file.Path ||| this.Worksheets.Add.Name = "File" & cnt ||| With ActiveWorkbook ||| .Worksheets(1).Range("A1:C100").Copy _ ||| Destination:=this.Worksheets("File" & cnt).Range("A1") ||| .Close ||| End With ||| cnt = cnt + 1 ||| End If ||| Next file ||| ||| End If ' sFolder < "" ||| ||| End Sub ||| ||| ||| ||| "Adri" wrote in message ||| ... |||| Bob Phillips wrote: ||||| Presumably you have modified the code? What does it look like now? ||||| And what Excel version do you have? |||| |||| Hi Bob, code in module 1 of c:\text.xls is (didn't modify it) |||| I'm working with Excel2000. |||| After Dim FSO as Object follws a line, is that correct? |||| |||| Option Explicit |||| |||| Dim FSO As Object |||| |||| Sub ProcessFiles() |||| Dim i As Long |||| Dim sFolder As String |||| Dim fldr As Object |||| Dim Folder As Object |||| Dim file As Object |||| Dim Files As Object |||| Dim this As Workbook |||| Dim cnt As Long |||| |||| Set FSO = CreateObject("Scripting.FileSystemObject") |||| |||| Set this = ActiveWorkbook |||| sFolder = "C:\MyTest" |||| If sFolder < "" Then |||| Set Folder = FSO.GetFolder(sFolder) |||| |||| Set Files = Folder.Files |||| cnt = 1 |||| For Each file In Files |||| If file.Type = "Microsoft Excel Worksheet" Then |||| Workbooks.Open Filename:=file.Path |||| this.Worksheets.Add.Name = "File" & cnt |||| With ActiveWorkbook |||| .Worksheets(1).Range("A1:C100").Copy |||| Destination:=this.Worksheets("File" & cnt).Range("A1") |||| .Close |||| End With |||| cnt = cnt + 1 |||| End If |||| Next file |||| |||| End If ' sFolder < "" |||| |||| End Sub |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
?addition
It's in each version of code I posted. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "keepITcool" wrote in message ... Adri, with the addition of 'dim fso as object' is runs fine for me too. see my elaborate answer to your multipost in nl.office.excel keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Tom Ogilvy" wrote: I recreated the environment you describe and ran the code. It ran fine for me. |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
I also asked the question in the Dutch newsgroup. There someone came up with
the solution. In the Dutche verion of Excel file the Type = "Microsoft Excel Worksheet" must be changed Type = "Microsoft Excel-werkblad". Now it runs fine. Thank you all. Adri Bob Phillips wrote: | ?addition | | It's in each version of code I posted. | | | "keepITcool" wrote in message | ... || Adri, || || || with the addition of 'dim fso as object' is runs fine for me too. || || see my elaborate answer to your multipost in nl.office.excel || || || keepITcool || || < email : keepitcool chello nl (with @ and .) || < homepage: http://members.chello.nl/keepitcool || || || "Tom Ogilvy" wrote: || ||| I recreated the environment you describe and ran the code. It ran ||| fine for me. |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy several range from all files in folder into several worksheets
That sounds like an excellent reason not to use the scripting runtime to do
this unless it is just for your personal use. -- Regards, Tom Ogilvy "Adri" wrote in message ... I also asked the question in the Dutch newsgroup. There someone came up with the solution. In the Dutche verion of Excel file the Type = "Microsoft Excel Worksheet" must be changed Type = "Microsoft Excel-werkblad". Now it runs fine. Thank you all. Adri Bob Phillips wrote: | ?addition | | It's in each version of code I posted. | | | "keepITcool" wrote in message | ... || Adri, || || || with the addition of 'dim fso as object' is runs fine for me too. || || see my elaborate answer to your multipost in nl.office.excel || || || keepITcool || || < email : keepitcool chello nl (with @ and .) || < homepage: http://members.chello.nl/keepitcool || || || "Tom Ogilvy" wrote: || ||| I recreated the environment you describe and ran the code. It ran ||| fine for me. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to create a copy of a folder having five files in it, & rename | Excel Worksheet Functions | |||
| Macro to copy range from Excel files in folder | Excel Discussion (Misc queries) | |||
| Copy files from spreadsheet into folder | Excel Discussion (Misc queries) | |||
| Copy same data from all files in folder | Excel Programming | |||
| Copy Files from a folder to new location | Excel Programming | |||
![Adri[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
