(Help debugging please) Running a macro for several files

[longtalker]

Hello everyone,

I have a macro that I would like to run for several txt files. The macro uses the Text To Columns command to arrange the numbers in the txt file into columns.

I had an old RunForAllFiles macro that I knew was able to run on several files code that was inserted at a certain point. So after I recorded the macro that does the TextToColumns, I included its code into the code of RunForAllFiles (see below), which was supposed to ask me for the folder where all the txt files are located, do the arranging for each file, then save each file as a CSV file.

However, when running the RunForAllFiles macro, I get a "Compile error: expected End Sub". Can anyone help me figure out what's wrong with the code? It's probably just a syntax error, but I know very little about VB and could not spot it myself. Many thanks in advance!

Sub RunAll()
Dim strPath As String
Dim strFile As String
Dim wbk As Workbook
Dim wsh As Worksheet
' Let user select a folder
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then
strPath = .SelectedItems(1)
Else
MsgBox "No folder selected", vbInformation
Exit Sub
End If
End With
Application.ScreenUpdating = False
If Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
' Loop through the files in the folder
strFile = Dir(strPath & "*.TXT")
Do While strFile <> ""
' Open the workbook
Set wbk = Workbooks.Open(Filename:=strPath & strFile, AddToMRU:=False)
Set wsh = wbk.Worksheets(1)



'''''''' INSERT HERE CODE THAT YOU WANT TO RUN FOR SEVERAL FILES
Sub separateCells()
 Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
        (20, 1), Array(21, 1), Array(22, 1)), TrailingMinusNumbers:=True
    Range("A1").Select
End Sub



' Save and close
wbk.SaveAs Filename:=strPath & Replace(strFile, ".TXT", ".XLSX"), _
FileFormat:=51 ' xlOpenXMLWorkbook
wbk.Close

' On to the next
strFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[jmthompson]

Well your code says:
Sub
Sub
End Sub
End Sub

You can't really do that, you probably want to take out this line

Sub separateCells()

and the End Sub before the Save and Close code.
</pre>

 

[longtalker]

So are you saying you can't have nested functions ("subs")? This seems strange, because I used this code several times, always replacing the middle sub with whatever macro I needed to run repeatedly for several files

 

[GTO]

A Sub or Function is not able to be nested, as they are a complete Procedure. You can call a Function or Sub repeatedly if needed. Neither can be nested.

 

[longtalker]

I see. Could you please recommend an easy way to repeat the TextToColumns procedure for several txt files that are all in one directory?