Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from many files
Hello,
Using Excel XP. I have a number of files in one directory (say C:\Temp). Each file has two sheets - "Infor" and "Sales". The layout and format of "Sales" is the same in all files. Example for 2 files follows. Cell C1 of the "Info" sheet in each workbook contains the customers name (= the name of each workbook respectively) (b) = blank cell ABC.xls - Sales worksheet (A1: C downwards) ItemCode ItemDesc Type1$ Type2$ 101 Item114 $101 (b) 102 Item102 $50 (b) 203 Item203 (b) $75 204 Item204 (b) $35 XYZ.xls - Sales worksheet (A1: C downwards) ItemCode ItemDesc Type1$ Type2$ 114 Item114 $23 (b) 102 Item102 $50 (b) 103 Item103 $56 (b) 203 Item203 (b) $75 204 Item204 (b) $35 205 Item205 (b) $45 From each file, I need to copy the data from each "Sales" worksheet to Sheet1 in "Summary_Sales.xls", in the following format: CustName ItemCode ItemDesc Type1$ Type2$ ABC 101 Item114 $101 (b) ABC 102 Item102 $50 (b) ABC 203 Item203 (b) $75 ABC 204 Item204 (b) $35 XYZ 114 Item114 $23 (b) XYZ 102 Item102 $50 (b) XYZ 103 Item103 $56 (b) XYZ 203 Item203 (b) $75 XYZ 204 Item204 (b) $35 XYZ 205 Item205 (b) $45 I am familiar with FileSearch to open/close each file in a directory, but I'm failing on the looping syntax to populate "Summary_Sales.xls". I have unsuccessfully tried to adapt the syntax from a macro authored Mr. T. Ogilvy found at http://makeashorterlink.com/?A14813CA8. I will appreciate any assistance on this matter. TIA Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data from many files
If I understand the problem correctly, you should be able to do it as a
nested loop, copying each cell, one at a time, from the Sales sheet of the file in question, to the Summary spreadsheet. I would write it as a separate subroutine to which you pass the name of the file. See if this makes sense: Sub Summarize() SummarizeFile "C:\Files\ABC.XLS" SummarizeFile "C:\Files\XYZ.XLS" End Sub Sub SummarizeFile(ByVal sFileName As String) Dim lrowcount As Long Dim lrow As Long Dim lrow_summary As Long Dim icolcount As Integer Dim icol As Integer ' first empty row in summary sheet: lrow_summary = ThisWorkbook.Worksheets(1).UsedRange.Rows.Count + 1 Workbooks.Open sFileName Worksheets("Sales").Activate ' determine how many rows and columns are used: lrowcount = ActiveSheet.UsedRange.Rows.Count icolcount = ActiveSheet.UsedRange.Columns.Count For lrow = 2 To lrowcount ThisWorkbook.Worksheets(1).Cells(lrow_summary, 1).Value = sFileName For icol = 1 To icolcount ThisWorkbook.Worksheets(1).Cells(lrow_summary, icol + 1).Value = _ ActiveSheet.Cells(lrow, icol).Value Next icol lrow_summary = lrow_summary + 1 Next lrow ActiveWorkbook.Close End Sub "David" wrote in message ... Hello, Using Excel XP. I have a number of files in one directory (say C:\Temp). Each file has two sheets - "Infor" and "Sales". The layout and format of "Sales" is the same in all files. Example for 2 files follows. Cell C1 of the "Info" sheet in each workbook contains the customers name (= the name of each workbook respectively) (b) = blank cell ABC.xls - Sales worksheet (A1: C downwards) ItemCode ItemDesc Type1$ Type2$ 101 Item114 $101 (b) 102 Item102 $50 (b) 203 Item203 (b) $75 204 Item204 (b) $35 XYZ.xls - Sales worksheet (A1: C downwards) ItemCode ItemDesc Type1$ Type2$ 114 Item114 $23 (b) 102 Item102 $50 (b) 103 Item103 $56 (b) 203 Item203 (b) $75 204 Item204 (b) $35 205 Item205 (b) $45 From each file, I need to copy the data from each "Sales" worksheet to Sheet1 in "Summary_Sales.xls", in the following format: CustName ItemCode ItemDesc Type1$ Type2$ ABC 101 Item114 $101 (b) ABC 102 Item102 $50 (b) ABC 203 Item203 (b) $75 ABC 204 Item204 (b) $35 XYZ 114 Item114 $23 (b) XYZ 102 Item102 $50 (b) XYZ 103 Item103 $56 (b) XYZ 203 Item203 (b) $75 XYZ 204 Item204 (b) $35 XYZ 205 Item205 (b) $45 I am familiar with FileSearch to open/close each file in a directory, but I'm failing on the looping syntax to populate "Summary_Sales.xls". I have unsuccessfully tried to adapt the syntax from a macro authored Mr. T. Ogilvy found at http://makeashorterlink.com/?A14813CA8. I will appreciate any assistance on this matter. TIA Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying from different files into 1 | Excel Discussion (Misc queries) | |||
Need Help Copying Excel Files to a CD | Excel Discussion (Misc queries) | |||
Copying Files | Excel Discussion (Misc queries) | |||
Copying files | Excel Worksheet Functions | |||
Copying data across 2 xcel files | Excel Programming |