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

 

[westconn1]

i don't know what the problem is you are having exactly, but you are only passing the filename to the workbooks.openText, you possibly need to pass the path as well

 

[tomtom02]

i don't know what the problem is you are having exactly, but you are only passing the filename to the workbooks.openText, you possibly need to pass the path as well

Thanks for the reply.
I thought the path would be included in strCurrentFile, because it is defined as:
strCurrentFile = Dir(strDocPath & MacID("TEXT"))
I must be missing something . . .

 

[tomtom02]

i don't know what the problem is you are having exactly, but you are only passing the filename to the workbooks.openText, you possibly need to pass the path as well

I've tried adding the path as well (I found another post with a similar method that posted the path, despite it being part of the definition of the filename).

I'm stuck here. If anyone has any advice, I would be deeply grateful!

 

[Ragnar1211]

I'm not at home so my Mac isn't near me but I have a question that might help:

If you record a macro that opens a file - what does the file path look like. My guess is that there is something wrong with the file path and it is returning "" therefore your macro thinks its done.

 

[tomtom02]

I'm not at home so my Mac isn't near me but I have a question that might help:

If you record a macro that opens a file - what does the file path look like. My guess is that there is something wrong with the file path and it is returning "" therefore your macro thinks its done.

Hi Ragnar, when I open through the file menu, recording a macro gives me thisI get the code below.

Sub open()
'
ChDir _
"Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv2:Loop_test:directory:"
Workbooks.OpenText Filename:= _
"Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv2:Loop_test:directory:3.txt" _
, Origin:=xlMacintosh, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
ActiveWindow.SmallScroll Down:=3
With ActiveWindow
.Top = 63
.Left = 382
End With
End Sub



I have been opening the same file with this code and it has been working:

Workbooks.OpenText Filename:= _
"Macintosh HD"":Users:thomasarmstrong:Desktop:SF08_Macrosv2:Loop_test:directory:3.txt", _
DataType:=xlDelimited, Tab:=True



Thanks for replying. I hope this provides some clues!

 

[Ragnar1211]

After this line:

strCurrentFile = Dir(strDocPath & MacID("TEXT"))

Put this:

MsgBox strCurrentFile


I just want to see if it is really cycling through your files. I wonder if that line could be changed to:

strCurrentFile = Dir(strDocPath, MacID("TEXT"))

Try that also.

 

[tomtom02]

After this line:

strCurrentFile = Dir(strDocPath & MacID("TEXT"))

Put this:

MsgBox strCurrentFile


I just want to see if it is really cycling through your files. I wonder if that line could be changed to:

strCurrentFile = Dir(strDocPath, MacID("TEXT"))

Try that also.

I put that code in, and a blank message box popped up. The cursor went to an hour glass, and stayed that way for a while until I pressed okay. Then it went away and nothing had changed. I'll try getting rid of the &

 

[tomtom02]

hmmm . . . I tried removing the & and using a comma, and I got this message:
run-time error '1004':
no list was found. select a single cell within your list, and then click the command again

with the debugger going straight to the end of that line of text, that is:
strCurrentFile = Dir(strDocPath, MacID("TEXT"))

 

[tomtom02]

I should add that one thing is happening after I run the macro: the first row in the workbook from which I'm running the code gets selected