Converting Excel Data to Text

Factual Questions
1

I have a need to convert a whole pile of Excel files into text, and will be doing this regularly. Is there a way to automate this process, either through an external program, or an Excel macro/script? As it is, I know nothing about Excel programming, but I am not looking forward to endless sessions of opening files and saving them as text individually. Thanks.

2

Here a (kinda rough) macro:



Sub ConvertFiles()

FolderName = InputBox("Enter the folder name")
On Error GoTo BadFolder
ChDir (FolderName)
On Error GoTo 0
Set fs = Application.FileSearch
With fs
    .LookIn = FolderName
    .Filename = "*.xls"
    If .Execute(SortBy:=msoSortByFileName, _
            SortOrder:=msoSortOrderAscending) > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For I = 1 To .FoundFiles.Count
            Workbooks.Open Filename:=.FoundFiles(I)
            NewFN = Left(.FoundFiles(I), InStr(.FoundFiles(I), ".") - 1) & ".txt"
            ActiveWorkbook.SaveAs Filename:=NewFN, FileFormat:=xlTextWindows
            ActiveWorkbook.Close savechanges:=False
        Next I
    Else
        MsgBox "There were no Excel files found in " & FolderName
    End If
End With
Exit Sub

BadFolder:
MsgBox "Folder does not exist!"

End Sub
3

ps - There will be a bit of a complication if the workbooks contain multiple sheets… this macro only saves the first sheet.

4

That works great! No trouble with multiple sheets – everything I need to export are on the first sheet. Now I need to tweak it slightly to move stuff to the right directories, but this is such a huge improvement already. I’m sure I can figure the rest out & not trouble you any further.

Thank you! Thank you! Thank you!

:slight_smile: & :cool: