Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pull data from multiple files and copy into one file
I used the macro copy function and created the following:
Workbooks.Open ("C:\2001.xls") Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Copy Windows("Macro_try2").Activate ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(columnOffset:=-1).Activate ActiveCell.FormulaR1C1 = "'2001" ActiveCell.Offset(columnOffset:=1).Activate Range(Selection, Selection.End(xlDown)).Select Windows("2001.XLS").Activate ActiveWorkbook.Close Windows("Macro_try2.XLS").Activate Selection.End(xlDown).Select ActiveCell.Offset(rowOffset:=1).Activate This works great for one file, but I have a directory with over 200 files that need the same action. How do I select the next file in the directory after finishing with one? As you can see the file name is used in 3 different lines in the macro. How do I go from one file to the next and update the macro with the current file name? I would like to be able to copy the file name down the column it is in for all rows out of that file, but even the relative copy function pastes data into a specific range. What code would I use to set the paste range equal to the number of rows copied? Any guidance is appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pull data from multiple files and copy into one file
Bob Philips recently posted this in response to a similar question. Perhaps
you can adapt it to your situation: Sub ProcessFiles() Dim oFSO As Object Dim i As Long Dim sFolder As String Dim fldr As Object Dim oFolder As Object Dim oFile As Object Dim oFiles As Object Dim this As Workbook Dim iRow As Long Dim oSh As Worksheet Dim rng As Range Set oFSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook Set oSh = ActiveSheet sFolder = "C:\MyTest" If sFolder < "" Then Set oFolder = oFSO.GetFolder(sFolder) Set oFiles = oFolder.Files For Each oFile In oFiles If oFile.Type = "Microsoft Excel Worksheet" Then Workbooks.Open FileName:=oFile.Path With ActiveWorkbook Set rng = _ .Worksheets(1).Range("A337:A383") _ .SpecialCells(xlConstants) iRow = oSh.Cells(Rows.Count, 2).End(xlUp) If iRow < 1 Then iRow = iRow + 1 rng.Copy Destination:=oSh.Cells(iRow, 2) .Close SaveChanges:=False End With End If Next oFile End If ' sFolder < "" End Sub -- Regards, Tom Ogilvy "JT" wrote in message ... I used the macro copy function and created the following: Workbooks.Open ("C:\2001.xls") Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Copy Windows("Macro_try2").Activate ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(columnOffset:=-1).Activate ActiveCell.FormulaR1C1 = "'2001" ActiveCell.Offset(columnOffset:=1).Activate Range(Selection, Selection.End(xlDown)).Select Windows("2001.XLS").Activate ActiveWorkbook.Close Windows("Macro_try2.XLS").Activate Selection.End(xlDown).Select ActiveCell.Offset(rowOffset:=1).Activate This works great for one file, but I have a directory with over 200 files that need the same action. How do I select the next file in the directory after finishing with one? As you can see the file name is used in 3 different lines in the macro. How do I go from one file to the next and update the macro with the current file name? I would like to be able to copy the file name down the column it is in for all rows out of that file, but even the relative copy function pastes data into a specific range. What code would I use to set the paste range equal to the number of rows copied? Any guidance is appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I pull data from multiple files and copy into one file
The Dir() function will list files in a directory. You use it by specifying
the path (the first time used) and then just using Dir with no argument until it returns a zero-length string ("") - Example: Dim MyFileName as String MyFileName = Dir("C:\*.xls") While MyFileName <"" ' Put your code here; use "C:\" & MyFileName wherever you need to use the file path Dir Wend As for the copy/paste, not sure if I understand what you are trying to do, but it sounds like you want the paste range to be the same size as the copied range??? If you copy a range and then paste into a specific cell, the size of the pasted range should match the size of the copied range. "JT" wrote: I used the macro copy function and created the following: Workbooks.Open ("C:\2001.xls") Range("A2").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Copy Windows("Macro_try2").Activate ActiveSheet.Paste Application.CutCopyMode = False ActiveCell.Offset(columnOffset:=-1).Activate ActiveCell.FormulaR1C1 = "'2001" ActiveCell.Offset(columnOffset:=1).Activate Range(Selection, Selection.End(xlDown)).Select Windows("2001.XLS").Activate ActiveWorkbook.Close Windows("Macro_try2.XLS").Activate Selection.End(xlDown).Select ActiveCell.Offset(rowOffset:=1).Activate This works great for one file, but I have a directory with over 200 files that need the same action. How do I select the next file in the directory after finishing with one? As you can see the file name is used in 3 different lines in the macro. How do I go from one file to the next and update the macro with the current file name? I would like to be able to copy the file name down the column it is in for all rows out of that file, but even the relative copy function pastes data into a specific range. What code would I use to set the paste range equal to the number of rows copied? Any guidance is appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to pull data from another file - update name of file | Excel Discussion (Misc queries) | |||
Pull data from a file | Excel Discussion (Misc queries) | |||
pull data for a company with data in diff cells multiple wrkshts | Excel Worksheet Functions | |||
Pull multiple data | Excel Worksheet Functions | |||
How to pull data out of an excell file with multiple tabs | Excel Worksheet Functions |