Runtime error when trying to save a sheet as a txt file

londa_vba

Board Regular
Joined
May 11, 2023
Messages
61
Office Version
  1. 365
Platform
  1. Windows
Hi
I have a specific sheet that I would like to create a macro to save a copy of only this sheet as a txt file without removing it from the current workbook.
Multiple people use this workbook so txt file name and folder location to save will vary and need to be chosen by the user.
I was working from the code below but I got a Runtime Error at line 11. See photo.

VBA Code:
Sub SaveSheetAsTextFile()
    Dim ws As Worksheet
    Dim strFile As String
    Dim strPath As String
    
    Set ws = ThisWorkbook.Sheets("Trio Quant Import (2)") ' Replace "Sheet1" with the name of your sheet
    strFile = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt", Title:="Save As Text File") ' Open the Save As dialog box
    If strFile <> "False" Then ' If the user clicked Save
        strPath = Left(strFile, InStrRev(strFile, "\")) ' Extract the path from the file name
        ws.Copy ' Copy the sheet to a new workbook
        ActiveWorkbook.SaveAs Filename:=strPath & Application.PathSeparator & Mid(strFile, InStrRev(strFile, "\") + 1), FileFormat:=xlTextWindows ' Save the new workbook as a text file with the user-specified name and location
        ActiveWorkbook.Close SaveChanges:=False ' Close the new workbook without saving changes
    End If
End Sub

RunTime.PNG
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi
I have a specific sheet that I would like to create a macro to save a copy of only this sheet as a txt file without removing it from the current workbook.
Multiple people use this workbook so txt file name and folder location to save will vary and need to be chosen by the user.
I was working from the code below but I got a Runtime Error at line 11. See photo.

VBA Code:
Sub SaveSheetAsTextFile()
    Dim ws As Worksheet
    Dim strFile As String
    Dim strPath As String
   
    Set ws = ThisWorkbook.Sheets("Trio Quant Import (2)") ' Replace "Sheet1" with the name of your sheet
    strFile = Application.GetSaveAsFilename(FileFilter:="Text Files (*.txt), *.txt", Title:="Save As Text File") ' Open the Save As dialog box
    If strFile <> "False" Then ' If the user clicked Save
        strPath = Left(strFile, InStrRev(strFile, "\")) ' Extract the path from the file name
        ws.Copy ' Copy the sheet to a new workbook
        ActiveWorkbook.SaveAs Filename:=strPath & Application.PathSeparator & Mid(strFile, InStrRev(strFile, "\") + 1), FileFormat:=xlTextWindows ' Save the new workbook as a text file with the user-specified name and location
        ActiveWorkbook.Close SaveChanges:=False ' Close the new workbook without saving changes
    End If
End Sub

View attachment 99878
It looks like you have added in the PathSeperator unnecessarily so you have two '\\' characters in the path.
 
Upvote 0
It looks like you have added in the PathSeperator unnecessarily so you have two '\\' characters in the path.
Hello thank you for your response; however, I do not understand your comment. My knowledge of code is minimal and I copied the above from an online source. Would you be willing to elaborate?
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top