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.
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
ps - There will be a bit of a complication if the workbooks contain multiple sheets… this macro only saves the first sheet.
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!