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 SubWas 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,926 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.