
Originally Posted by
rcm
I wrote a macro to do what you requested..
in order to run properl you need to adjust
path: the actual path were all your files are
fname: the real name for the files hopefully there is pattern on the names
Ok, I'm looking at the code below in Module1:
Sub decode()
On Error Resume Next
shout = "sheet3"
Data = "sheet1"
Path = "c:\....."
For wrkb = 1 To 100
fname = "Workbook" & Right("000" & wrkb, 3) & ".xls"
Workbooks.Open fname
r = 2
r2 = 2
While Sheets(Data).Cells(r, 1) <> ""
st = Sheets(Data).Cells(r, 1)
c = 1
j = 0
j = Application.WorksheetFunction.Find(" ", st)
While j > 0
Sheets(shout).Cells(r2, c + 10) = Left(st, j - 1)
c = c + 1
st = Mid(st, j + 1, Len(st))
j = 0
j = Application.WorksheetFunction.Find(" ", st)
Wend
Sheets(shout).Cells(r2, c + 10) = st
Sheets(shout).Cells(r2, 10) = "=Sum(k" & r2 & ":t" & r2 & ")"
Sheets(shout).Cells(r2, 21) = "=t" & r2 & "/k" & r2
r2 = r2 + 1
r = r + 1
Wend
Workbooks.Close
Next wrkb
End Sub
Am I looking at the right thing? I change the path the path at:
right?
The files are all named Book2.xlsx through Book174.xslx....
At this line:
fname = "Workbook" & Right("000" & wrkb, 3) & ".xls"
I have to change ".xls" to ".xlsx", right?
If they are all named Book2.xlsx through Book174.xslx.... how do I make a reference to the filenames? Thanks, rcm... Appreciate it!
Bookmarks