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.
- Copy the below VBA Code by clicking on the copy box
- Open Microsoft Visual Basic for Applications window Developer Ribbon Tab > Visual Basic (Shortcut keys as follows for mac & windows)
- mac users fn + ⌥ [option] + F11
- windows users Alt + F11
- From the menu bar select Insert > Module and insert new Modules
- 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.
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.