Microsoft Office Forums - View Single Post - Macro to Save as Text to External Data Source Directory

Baiano42 · 06-29-2019, 09:42 PM

So this was the macro I had been using to save as text before:

Code:

Option Explicit

Sub SaveSheetToTxt()
'UpdatebyBaiano42
    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

The problem with this code is that it is set to save to "ThisWorkbook.Path". Which, as the WB has not been saved anywhere, it does not have a path to reference to. (See attached images for further reference).

So I do have the 'path' in a sense. However, as I am constantly building new projects, I'm trying to make the macro be able to direct the new .txt document save to the current external source document I have sourced to the WB.