Loop over folder of workbooks and export all sheets to tab-delimited text with Excel VBA

Question

I pieced together an Excel VBA script that writes all worksheets in an open workbook to separate, tab-delimited files (is this still a "macro"? I'm learning this in an Excel vacuum). It works well on one workbook at a time. Here it is.

Sub exportSheetsToText()
    Dim sWb As String
    Dim sFile As String
    Dim oSheet As Worksheet

    sWb = Left(ActiveWorkbook.FullName, InStr(ActiveWorkbook.FullName, ".") - 1)

    For Each oSheet In Worksheets
        oSheet.Copy
        sFile = sWb & "-" & oSheet.Name & ".txt"
        ActiveWorkbook.SaveAs fileName:=sFile, FileFormat:=xlText
        ActiveWorkbook.Close SaveChanges:=False
        Next oSheet
End Sub

I would like to scale this up so that I can apply this macro to a folder of workbooks. I wrote what I thought would loop over every workbook that satisfies the filter, but it doesn't write any of the .txt files. Here it is.

Sub exportsSheetsToTextForAll()

    Dim sPath As String
    Dim sWildcard As String
    Dim sMacro As String
    Dim oWb As Workbook
    Dim oPersWb As Workbook

    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Set oPersWb = Workbooks("PERSONAL.XLSB")
    sMacro = "'" & oPersWb.Name & "'" & "!exportSheetsToText()"
    sPath = "C:\Users\richard\Documents\Research\Data\Excel\Datastream - payout"
    sWildcard = "New*.xlsx"
    sFile = Dir(sPath & "\" & sWildcard)


    Do While Len(sFile) > 0
        Workbooks.Open Filename:=sPath & "\" & sFile
        Application.Run sMacro
        ActiveWorkbook.Close SaveChanges:=False
        sFile = Dir
    Loop

End Sub

It loops through all of my test files, but I don't see any effects (i.e., no .txt files and no errors).

Eventually I will run this on very large workbooks with macros, so it is important to disable the macros (I don't have the macros locally, they're on a dedicated data machine) and close one large workbook before opening the next.

Any ideas? Thanks!

I have just given it a birds view as I have to step out any moment now. Here is one suggestion though... Instead of Workbooks.Open Filename:=sPath & "\" & sFile try Set oWB = Workbooks.Open (Filename:=sPath & "\" & sFile) and then Also amend your macro to take wb as a parameter so exportSheetsToText() becomes Sub exportSheetsToText(wb as workbook) Once this is done. Do not work with Activeworkbook in exportSheetsToText(). Use wb instead. — Siddharth Rout
@Philip - Yes. I made some barebones "New Microsoft Excel Worksheet.xlsx" files from the Windows Explorer context menu and populated them with a few columns of RAND(). — Richard Herron
@SiddharthRout - Thanks. I'm working on these suggestions. In general, I'm confused when VBA needs (), when it needs =, and when it needs :=. — Richard Herron
exportSheetsToText will always export the files to the current directory, so maybe they're going somewhere other than where you expect to find them? Try adding a Debug.Print ActiveWorkbook.Path after saving, and see what it produces. — Tim Williams

Richard Herron Richard Herron · Accepted Answer · 2013-04-12T16:34:49

@Siddarth's idea of passing an argument to exportSheetsToText() was the key. As well I had an error with macro name passed to Application.Run. The following works and is much cleaner.

Sub exportsSheetsToTextForAll()

    Application.AutomationSecurity = msoAutomationSecurityForceDisable

    excelFiles = Dir(ThisWorkbook.Path & "\" & "New*.xlsx")
    fromPath = ThisWorkbook.Path

    Do While Len(excelFiles) > 0
        Debug.Print Files
        Set oWb = Workbooks.Open(Filename:=fromPath & "\" & excelFiles)
        Application.Run "exportSheetsToText", oWb
        oWb.Close SaveChanges:=False
        excelFiles = Dir
    Loop

End Sub

Sub exportSheetsToText(iWb As Workbook)

    For Each ws In iWb.Worksheets
        ws.Copy
        Set wb = ActiveWorkbook
        textFile = Left(iWb.FullName, InStr(iWb.FullName, ".") - 1) & "-" & ws.Name & ".txt"
        wb.SaveAs Filename:=textFile, FileFormat:=xlText
        wb.Close SaveChanges:=False
    Next ws
End Sub

Loop over folder of workbooks and export all sheets to tab-delimited text with Excel VBA

1 Answers