Saving Current Worksheet as Text in Current Directory

[Baiano42]

I currently have a macro that lets me save my sheet using the current sheet's data and tab name as text, which I hope to keep. However, when I try to save it, it automatically takes me to the 'My Documents' folder. Could someone please help me make the code automatically direct the new save as to the current directory of the Excel file? Thanks!

Sub SaveSheetToTxt()
'Updateby20150910
Dim xRet As Long
Dim xFileName As Variant
On Error GoTo ErrHandler:
xFileName = Application.GetSaveAsFilename(ActiveSheet.Name, "Text (Tab Delimited) (*.txt), *.txt", , "Kutools for Excel")
If xFileName = False Then Exit Sub
If Dir(xFileName) <> "" Then
xRet = MsgBox("File '" & xFileName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel")
If xRet <> vbYes Then
Exit Sub
Else
Kill xFileName
End If
End If
ActiveSheet.Copy
ActiveWorkbook.SaveAs xFileName, xlUnicodeText
If ActiveWorkbook.Name <> ThisWorkbook.Name Then
ActiveWorkbook.Close False
End If
My_Exit:
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Kutools for Excel"
End Sub

Cheers,

Baiano42

[Paul_Hossler]

If I run this on my Desktop, it opens the GetSaveAsFilename dialog there. Is that what you were looking to do?

Option Explicit

Sub SaveSheetToTxt()
'Updateby20150910
    
    Dim xRet As Long
    Dim xFileName As Variant
    Dim wbText As Workbook

    On Error GoTo ErrHandler:

    ChDir ThisWorkbook.Path                '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

    xFileName = Application.GetSaveAsFilename(ActiveSheet.Name, "Text (Tab Delimited) (*.txt), *.txt", , "Kutools for Excel")

    If xFileName = False Then Exit Sub
    
    If Dir(xFileName) <> "" Then
        xRet = MsgBox("File '" & xFileName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel")
        
        If xRet <> vbYes Then
            Exit Sub
        Else
            Application.DisplayAlerts = False
            Kill xFileName
            Application.DisplayAlerts = True
        End If
    End If
    ActiveSheet.Copy
    Set wbText = ActiveWorkbook
    
    wbText.SaveAs xFileName, xlUnicodeText
    wbText.Close False
My_Exit:
    Exit Sub
ErrHandler:
    MsgBox Err.Description, , "Kutools for Excel"
End Sub

[Baiano42]

Paul, thank you for your reply,
Unfortunately, that took me to
...\AppData\Roaming\Microsoft\Excel\XLSTART

I've been tinkering with another set of code, and it too seems to be saving it to the same area:

Sub SaveAsTxt()
'
' UpdateByBaiano42
'
    ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name, FileFormat:=xlText, CreateBackup:=False
End Sub

It is shorter, saves the data like the previous set of code provided, and it too saves to the ...\app data\roming\... and not the current directory like I'm wanting... And this set of code does the same too:

Sub SaveAsTxt()
' SaveAsText Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & ActiveSheet.Name & ".txt", _
    FileFormat:=xlText, CreateBackup:=False
    Application.DisplayAlerts = True
    ActiveWorkbook.Close SaveChanges:=True
End Sub

Still gets the save as text, to the name of the sheet, but still not directed to the current directory.

[Paul_Hossler]

What folder is the macro containing Excel file in?

Could someone please help me make the code automatically direct the new save as to the current directory of the Excel file? Thanks!

What do you mean by "Current Directory"?

If my XLSM is in Desktop, then the dialog uses

ChDir ThisWorkbook.Path '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


to start in Desktop

Capture.JPG

[Baiano42]

I have my macros saved in my Macro Book, because I need them to be used in multiple work books. The current XL Work book I'm working with is saved in '\Documents\Current Jobs\6-28-19 Sally\Plans'. I often am changing folder directories, and need the macro to keep up with it. Tomorrow the folder may be ...\6-29-9 Sally\...
'Current Directory' means the folder where the XL spreadsheet is located that contains the current data I'm trying to save as text.

[Paul_Hossler]

You didn't say that the macro was in your Personal.xlsm

Try changing the line to this then and see if it works

ChDir ActiveWorkbook.Path '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


If you are running a macro in your Personal.xlsm, ThisWorkbook.Path would be XLSTART

[Baiano42]

Unfortunately, the

ChDir ActiveWorkbook.Path '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Still tries to save it to the XLSTART folder. Sadly, I need the macro to be saved there, cause I need to use it on different documents as I use multiple workbooks that other people generate and don't have any macros saved to it. Any other tricks that I could try?

[Baiano42]

Is there a way to define the current directory to where the workbook containing the data is located, and not where the the macro is located, and then have it save as text?

[Baiano42]

NVM, I got it to work with the ChDir ActiveWorkbook.Path '<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Thanks Paul, you are amazing!
One more question, I also need to open text files occasionally, and need to do some adjustments to those files. When trying to save them back as a text, the code won't quite work, as the Workbook hasn't been saved anywhere. Current work flow is:
Open excel->Open from text->(Do edits)->Save As->Save in folder
However, using that method, the ActiveWorkbook hasn't been saved, so there is no target to save to.

Question: how can I set the save target to the source text file?

[Paul_Hossler]

The Activeworkbook.Path is the path to the WB that has the sheet in it, not the macro containing one in XLSTART (unless that's where you put the sheet WB)

It works for me

I made a hidden WB with the macro and saved it to XLSTART

Opened another XLXS on the Desktop with a WS and ran the macro and it saved the TXT file to the Desktop

Opened another XLXS inDocuments with a WS and ran the macro and it saved the TXT file to Documents

BTW I don't know why you're using GetSaveAsFileName. No needed since you have the Path from ActiveWorkbook and the file name from the WS name

Look at SaveAsText2 below

Capture.jpg

Option Explicit
Sub SaveAsText1()
    
    Dim xRet As Long
    Dim xFileName As Variant
    Dim wbText As Workbook
    On Error GoTo ErrHandler
    ChDir ActiveWorkbook.Path
    xFileName = Application.GetSaveAsFilename(ActiveSheet.Name, "Text (Tab Delimited) (*.txt), *.txt", , "Kutools for Excel")
    MsgBox xFileName

    If xFileName = False Then Exit Sub
    
    If Dir(xFileName) <> "" Then
        xRet = MsgBox("File '" & xFileName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel")
        
        If xRet <> vbYes Then
            Exit Sub
        Else
            Application.DisplayAlerts = False
            Kill xFileName
            Application.DisplayAlerts = True
        End If
    End If
    ActiveSheet.Copy
    Set wbText = ActiveWorkbook
    
    wbText.SaveAs xFileName, xlUnicodeText
    wbText.Close False
My_Exit:
    Exit Sub
ErrHandler:
    MsgBox Err.Description, , "Kutools for Excel"
End Sub




Sub SaveAsText2()
    
    Dim xRet As Long
    Dim xFileName As Variant
    Dim wbText As Workbook
    xFileName = ActiveWorkbook.Path & Application.PathSeparator & ActiveSheet.Name & ".txt"
    MsgBox xFileName    '   remove later <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    If Dir(xFileName) <> "" Then
        xRet = MsgBox("File '" & xFileName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel")
        
        If xRet <> vbYes Then
            Exit Sub
        Else
            Application.DisplayAlerts = False
            Kill xFileName
            Application.DisplayAlerts = True
        End If
    End If
    
    ActiveSheet.Copy
    Set wbText = ActiveWorkbook
    
    wbText.SaveAs xFileName, xlUnicodeText
    wbText.Close False
    
End Sub

If that isn't it, then please add a lot more details

[Baiano42]

So here is the current workflow that I'm using (See below):

Imported Text Workflow.jpg

Using the SaveAsText2, I receive the following prompt:

Imported Text Workflow_b.jpg

And clicking Debug when prompted gives this:

Imported Text Workflow_c.jpg

[Paul_Hossler]

If the workbook with the data is open and has been previously saved, then could you just do it like this?

This uses the Path of the opened WB, not the one containing the macro, and the sheet name

Option Explicit


Sub SaveAsText4()
    Dim sPath As String, sFilename As String, sOutputName As String
    
    sPath = ActiveWorkbook.Path
    sFilename = ActiveSheet.Name
    sOutputName = sPath & Application.PathSeparator & sFilename & ".txt"

    MsgBox sOutputName    '   testing
    
    If Dir(sOutputName) <> "" Then
        If MsgBox("File '" & sOutputName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel") <> vbYes Then Exit Sub
        
        Application.DisplayAlerts = False
        Kill sOutputName
        Application.DisplayAlerts = True
    End If
    
    ActiveWorkbook.SaveAs sOutputName, xlUnicodeText
    ActiveWorkbook.Close False
End Sub

[Baiano42]

That would work, but in this case, I'm hoping to either save a new .txt file or overwrite the old one to the external source folder (which changes per project I'm working on), without needing to save the excel WB. Having to save the WB in this case would entail manually finding and saving it to the external source folder, which is the part that I'm hoping to automate now.

[Paul_Hossler]

So you want to ...


1. Have the macro in XLSTART

2. Select a TXT file (input.txt)

3. Load TXT into new WB (Book1)

4. Are you going to do something with the file once it's loaded??

5. Save new TXT file to same folder as input.txt with name = the sheet name

6. Close w/o saving Book1

?????

I currently have a macro that lets me save my sheet using the current sheet's data and tab name as text, which I hope to keep. However, when I try to save it, it automatically takes me to the 'My Documents' folder. Could someone please help me make the code automatically direct the new save as to the current directory of the Excel file? Thanks!

I guess I'm hung up / confused by the above. If you haven't saved the Excel file, then it's not in a directory


In my #12, no workbook needs to be saved, just the input needs to be open. That part can be automated to allow user select using GetOpenFileName().

[Baiano42]

That is correct, I hope to do the following steps:

1. Have the macro in XLSTART

2. Select a TXT file (input.txt)

3. Load TXT into new WB (Book1)

4. Run a macro to process my data (I've got it to run a couple equations and filter out the data I don't need successfully)

5. Save new TXT file to same folder as input.txt with name = the sheet name

6. Close w/o saving Book1

(Ideally, I'd like to combine the macros for step 4 - 6, so that it would do the processing and then save it with one click)

Pardon me, but I'm not sure I follow. When you noted that the input needs to be open, and it can be automated, what do you mean by that? (I am very new to making macros, and am very grateful for your patience and assistance Paul).

[Paul_Hossler]

Try this -

Option Explicit
Sub SaveAsText6()
    Dim sPath As String, sFilename As String, sOutputName As String, sInputName As String
    Dim i As Long
    
    'get text file name from user, exit if canceled
    sInputName = Application.GetOpenFilename("Text (Tab Delimited) (*.txt), *.txt", , "Kutools for Excel")
    If sInputName = "False" Then Exit Sub
    
    MsgBox sInputName    '   testing
        
    'open text file
    Workbooks.Open Filename:=sInputName
    
    
    'do some processing
    ActiveSheet.Name = "Output"
    ActiveSheet.Cells(1, 1).CurrentRegion.Value = 3456
    
    
    'get path
    i = InStrRev(sInputName, "\")
    sPath = Left(sInputName, i)
    sFilename = ActiveSheet.Name
    sOutputName = sPath & sFilename & ".txt"
    MsgBox sOutputName    '   testing
    
    If Dir(sOutputName) <> "" Then
        If MsgBox("File '" & sOutputName & "' exists. Overwrite?", vbYesNo + vbExclamation, "Kutools for Excel") <> vbYes Then Exit Sub
        
        Application.DisplayAlerts = False
        Kill sOutputName
        Application.DisplayAlerts = True
    End If
    
    ActiveWorkbook.SaveAs sOutputName, xlUnicodeText
    ActiveWorkbook.Close False
End Sub

[NoSparks]

@ Baiano42
seeing your picture in post #11 and at MSOfficeForums, is it reasonable to think steps 2 & 3 of post #15 should be
2. open a new blank workbook (Book1)
3. load text into Book1 using the 'From Text' icon on the Data Ribbon

[Baiano42]

@ Paul_Hossler
It's so the latest code set you provided gives me an "Open" window and is directed towards the Desktop when run. (See first image)

@ NoSparks
You are correct, stating it as follows would be more accurate:
1. Have the macro in XLSTART

2. open a new blank workbook (Book1)

3. load text into Book1 using the 'From Text' icon on the Data Ribbon

4. process data

5. Save new TXT file to same folder as input.txt with name = the sheet name (This would need to be a relative link, as I may be using different input.txt files in different sheets.)

6. Close w/o saving Book1

Ideally, I'd like to make it:
1. Have the macro in XLSTART

2. open a new blank workbook (Book1)

3. load text into Book1 using the 'From Text' icon on the Data Ribbon

4. Use macro to process data, Save new TXT file to same folder as input.txt with name = the sheet name, Close w/o saving Book1

*Aside: Looking at the data connection, I see that the source file directory is linked in the 'Definition: Connection file' and 'Edit Query' (See second image). Is there any way to make a relative link through either of those to get the save file to be directed to the desired location?*

SaveAsTxt6 Run.JPG

External Data Source Location.JPG

[Paul_Hossler]

@ Paul_Hossler It's so the latest code set you provided gives me an "Open" window and is directed towards the Desktop when run. (See first image)

Yes, but you have to change folders to where ever the text file is, open it, etc.

Saving the result file will be in that folder (i.e. the same as input) with the sheet name used as the file name


There's no need for a blank workbook since the macro opens the text file and creates it's own workbook


I thought that's what you wanted

[Baiano42]

Yes, but you have to change folders to where ever the text file is, open it, etc.

Not quite, so when I click to open the external source file, it creates an "Output.txt" file with a whole bunch of "3456" filling it.

Tinkering around with developer mode, I found this:

With ActiveWorkbook.Connections("Surface_A")
        .Name = "Surface_A"
        .Description = ""
    End With
    Range("A1:K41").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;C:\Users\Bill Gates\Documents\Current Jobs\6-28-19 Sally\Plans\Surface_A.txt"

Is there any way to be able to use parts of the above code as a way to achieve step 5, while still maintaining a relative folder reference (as I'll be using different connections for different projects)?