How to Save Excel File to Text File using VBA [Excel for mac and windows]

This VBA Code will save the Excel active worksheet to text file of your current workbook location. This code can use it for Excel for mac & windows.

  1. Copy the below VBA Code by clicking on the copy box
  2. Open Microsoft Visual Basic for Applications window Developer Ribbon Tab > Visual Basic (Shortcut keys as follows for mac & windows)
    1. mac users fn + ⌥ [option] + F11
    2. windows users Alt + F11
  3. From the menu bar select Insert > Module and insert new Modules
  4. Paste the code into the module.

Note: It is always good to take a back-up copy of your original before running the code on the original file.

Tip : You can copy and store this code into your personal macro workbook and use it as needed.

Sub SaveTheFileText()
'Made It Easy by ExcelExciting.com | Faraz Shaikh | Updated : 20200814

Dim FilePath As String
Dim FileName As String
Dim OS_Separator As String
Dim CheckOS As String
Dim FileExtCheck As String

    '******************************************************
    'Validation Check active workbook file path missing
        If ActiveWorkbook.Path = vbNullString Then
            MsgBox "Please save the workbook to run the code", vbCritical, "Excel Exciting | Faraz Shaikh"
            Exit Sub
        End If
    '******************************************************
    
    '******************************************************
    'Check the OS to add the path separator accordingly andI
        CheckOS = Application.OperatingSystem
        If CheckOS Like "*Mac*" Then
            OS_Separator = "/"  'for Mac
        Else
            OS_Separator = "\"  'for Windows
        End If
    '******************************************************
    
    FilePath = ActiveWorkbook.Path
    ChDir FilePath
    
    FileExtCheck = Mid(ActiveWorkbook.Name, InStr(LCase(ActiveWorkbook.Name), "."), (Len(ActiveWorkbook.Name) - InStr(LCase(ActiveWorkbook.Name), ".") + 1))
    
    If FileExtCheck <> ".xlsx" Then
        MsgBox "Unable to Run the code as the file is not in xlsx format." & vbCrLf & _
        " Please save to xlsx format and run the code", vbCritical, "Excel Exciting | Faraz Shaikh"
        Exit Sub
    Else
        If Mid(ActiveWorkbook.Name, InStr(LCase(ActiveWorkbook.Name), ".xlsx"), 5) = ".xlsx" Then
            FileName = Left(ActiveWorkbook.Name, InStr(LCase(ActiveWorkbook.Name), ".xlsx") - 1)
        Else
            FileName = Left(ActiveWorkbook.Name, InStr(LCase(ActiveWorkbook.Name), ".xls") - 1)
        End If
    End If
    
    ActiveWorkbook.SaveAs FileName:=FilePath & OS_Separator & FileName & ".txt", _
    FileFormat:=xlText, CreateBackup:=False
    
    MsgBox "File Saved Sucessfully" & vbCrLf & "File Location : " & FilePath & OS_Separator & FileName & ".txt", _
    vbInformation, "Excel Exciting | Faraz Shaikh"
        
End Sub

Was the information helpful in this blog?

  • If yes, hit that share button and show the excitement to the world.
  • Subscribe to our free posts.

Join 2,924 other subscribers

If no, please let us know what to improve.

Having trouble with any Office Apps. Feel free to ask and answer queries at our forums section.


2 1 vote
Article Rating
Subscribe
Notify of
guest
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Fede

Hey, great Macro, but I can`t save it on a Macro enabled spreadsheet, it says it ahs to be xlsx, but in that case I have to manually copy it everytime I need to use it. Any chance it can be changed to be able to use it on a Macro Enables spreadsheet?

Thanks.

1
0
Would love your thoughts, please comment.x
()
x