Copying data from many files

[David]

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

 

[Mark Thorpe]

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