Loop through folder, run macro on all files

[tomtom02]

Hello.
I am trying to process 60+ data files. I've recorded/written macros that do all of the processing, and now I would like to write a loop that will go through a folder with the data files (.txt, tab delimitted), and run the code on each file. I am using the Mac Version of Excel 2004. I have tried to adapt some code that I found in the forums, but after fixing a few errors, now nothing is happening when I run the code. The code is below. I have it in a module in the VBA editor. Any help would be greatly appreciated! -Tom
P.S. I apologize for the length of the code; I'm assuming the problem is in the very beginning or end (the code I added for the loop), as the code in the middle that does the actual processing works.

Sub ProcessData()

Dim strDocPath As String
Dim strCurrentFile As String

strDocPath = "Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv2:Loop_test:directory:"
strCurrentFile = Dir(strDocPath & MacID("TEXT"))

Do While strCurrentFile <> ""
'my code begins here
Workbooks.OpenText Filename:=strCurrentFile, _
DataType:=xlDelimited, Tab:=True
sSaveName = ActiveSheet.Name
ActiveSheet.Name = "Sheet1"
Sheets.Add
Sheets.Add
Sheets("Sheet1").Select
Cells.Select
Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Columns("Q:T").Select
Range("T1").Activate
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select

'rnd2

Range("U2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=1,"""",RC[-15])"
Range("U3").Select
Range("U2").Select
ActiveCell.FormulaR1C1 = "=IF(RC20=1,"""",RC[-15])"
Range("U2").Select
Selection.Copy
Range("U2:AH2").Select
ActiveSheet.Paste
Range("AH2").Select
Range("U2:AH2").Select
Application.CutCopyMode = False
Selection.Copy
Range("U2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=IF(RC20=1,"""",RC[-15])"
Range("U2:AH2").Select
Selection.Copy
Range("U2:AH65").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("U:AH").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select

'rnd3

Range("U2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-3]=0,"""",RC[-3])"
Range("U3").Select
Range("U2").Select
Selection.Copy
Range("V2").Select
ActiveSheet.Paste
Range("U2:V2").Select
Application.CutCopyMode = False
Selection.Copy
Range("U2:V65").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("R2").Select
ActiveWindow.SmallScroll Down:=-53
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("U:V").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("U2").Select
ActiveCell.FormulaR1C1 = "=IF(SUM(RC6:RC17)=0,"""",RC[-15])"
Range("U2").Select
Selection.Copy
Range("U2:AF2").Select
ActiveSheet.Paste
Range("U2:AF2").Select
Application.CutCopyMode = False
Selection.Copy
Range("U2:AF65").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("F2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("U:AF").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("A1").Select

'rnd4

Sheets("Sheet2").Select
Sheets("Sheet2").Name = "d"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "f"
Sheets("f").Select
Sheets.Add
Sheets.Add
Sheets("Sheet4").Name = "h"
Sheets("Sheet5").Name = "FINAL"
Sheets("Sheet1").Select
Range("F1:S1").Select
Range("S1").Activate
Selection.Copy
Sheets("d").Select
ActiveSheet.Paste
Range("A1:L1").Select
Range("L1").Activate
Application.CutCopyMode = False
Selection.Replace What:="l", Replacement:="_d", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="r", Replacement:="_nd", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="du_nd", Replacement:="dur", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("M1").Select
ActiveCell.FormulaR1C1 = "LAT_FF2_d"
Range("N1").Select
ActiveCell.FormulaR1C1 = "LAT_FF2_nd"
Range("N2").Select
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(Sheet1!RC3=""L"",Sheet1!RC1=""d""),Sheet1!RC[5])"
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(Sheet1!RC3=""L"",Sheet1!RC1=""d""),Sheet1!RC[5],"""")"
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(Sheet1!RC3=""L"",Sheet1!RC1=""d""),Sheet1!RC[5],"""")"
Range("B2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(Sheet1!RC3=""L"",Sheet1!RC1=""d""),Sheet1!RC[5],"""")"
Range("A2:B2").Select
Range("B2").Activate
Selection.Copy
Range("A2:B33").Select
ActiveSheet.Paste
Range("A33:B33").Select
Range("B33").Activate
Application.CutCopyMode = False
Selection.Copy
Range("A34:B34").Select
ActiveSheet.Paste
Range("A34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(AND(Sheet1!RC3=""R"",Sheet1!RC2=""d""),Sheet1!RC[6],"""")"
Range("B34").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(Sheet1!RC3=""R"",Sheet1!RC2=""d""),Sheet1!RC[4],"""")"
Range("A34:B34").Select
Range("B34").Activate
Selection.Copy
Range("A34:B65").Select
ActiveSheet.Paste
Range("A2:B65").Select
Application.CutCopyMode = False
Selection.Copy
Range("C2:N65").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Sheets("d").Select
Range("F64").Select
Sheets("Sheet1").Select
Sheets("d").Select
Sheets("Sheet1").Select
Sheets("d").Select
Range("A66").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-64]C:R[-1]C)/COUNT(R[-64]C:R[-1]C)"
Range("A66").Select
Selection.Copy
Range("A66:D66").Select
Range("A66:N66").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("f").Select
ActiveSheet.Paste
Sheets("h").Select
ActiveSheet.Paste
Sheets("f").Select
Rows("1:1").Select
Range("A1:L1").Select
Range("L1").Activate
Application.CutCopyMode = False
Selection.Replace What:="_d", Replacement:="_f", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="_nd", Replacement:="_nf", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("M1").Select
ActiveCell.FormulaR1C1 = "LAT_FF2_f"
Range("N1").Select
ActiveCell.FormulaR1C1 = "LAT_FF2_nf"
Sheets("h").Select
Selection.RowHeight = 12
Selection.RowHeight = 12
Range("A1:L1").Select
Range("L1").Activate
Selection.Replace What:="_nd", Replacement:="_nh", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="_d", Replacement:="_h", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("M1").Select
ActiveCell.FormulaR1C1 = "LAT_FF2_h"
Range("N1").Select
ActiveCell.FormulaR1C1 = "LAT_FF2_nh"
Range("A1").Select
Sheets("f").Select
Range("G2:N55").Select
Range("A2:N66").Select
Selection.Replace What:="""d""", Replacement:="""f""", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A66").Select
Sheets("d").Select
Sheets("d").Select
Sheets("f").Select
Sheets("d").Select
Sheets("h").Select
Range("A2:N66").Select
Selection.Replace What:="""d""", Replacement:="""h""", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Range("A1").Select
Sheets("f").Select
Sheets("d").Select
Range("A1").Select
Sheets("FINAL").Select
ActiveCell.FormulaR1C1 = "subject"
Sheets("d").Select
Range("A1:N1").Select
Selection.Copy
Sheets("FINAL").Select
Range("B1").Select
ActiveSheet.Paste
Sheets("d").Select
Range("A66:N66").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FINAL").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("f").Select
Range("A1:N1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FINAL").Select
Range("P1").Select
ActiveSheet.Paste
Sheets("f").Select
Range("A66:N66").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FINAL").Select
Range("P2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("h").Select
Range("A1:N1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FINAL").Select
Range("AD1").Select
ActiveSheet.Paste
Sheets("h").Select
Range("A66:N66").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("FINAL").Select
Range("AD2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A2").Select
Range("A2").Value = sSaveName
Range("A2").Replace _
What:=".txt", Replacement:=""
ActiveWorkbook.SaveAs Filename:= _
"Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv2:Loop_test:directoryrocessed:" & sSaveName _
, FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close False

'my code ends here

strCurrentFile = Dir
Loop

End Sub

 

[Ragnar1211]

From the help files:

<title>Dir Function</title><style>@import url(office.css);</style><link disabled="" href="msoffice.css" rel="stylesheet">
<code>Dir("SomePath", MacID("TEXT")) </code></pre> To iterate over all files in a folder, specify an empty string:

<code>Dir("") </code></pre>
Why not just create a folder with the files you want to process and not qualify the MacID?

 

[westconn1]

dir only returns the filename, no path
if you get an empty message box looks like the file is not found

try passing the empty string as in the post above, see if any file name is returned in the messagebox, if not looks like your path string is incorrect

 

[tomtom02]

Hello,
For all the mac users having trouble looping through a directory, as I did, here is a solution I came up with. I paste the filenames into the first column of the workbook that contains this macro.
Sub DirectoryLoopThrough()
Dim filename As String
Dim I As Integer
I = 1
Do
Cells(I, "A").Select
'the directory that your files are in goes below
filename = ActiveCell.Value
Workbooks.OpenText filename:= _
"Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv1:directory:raw:" & filename, _
DataType:=xlDelimited, Tab:=True


'Your code goes here


'the place you want the files saved to goes below
ActiveWorkbook.SaveAs filename:= _
"Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv1:directoryrocessed:" & filename
, FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Close False

I = I + 1
Loop Until (Cells(I, "A").Value = "")



End Sub

 

[Ragnar1211]

I'll post a solution later that does this with an array, a little cleaner.

Working now though..

Good job!