Modifying macro - save workbook as text delimited file

[dpnab]

I have the following code that saves the sheet as values into a new excel file (xlsx). I need to modify it so it saves it as a text file (tab delimited) instead. What do I need to change to make that happen? Thank you!

Sub Export_Sheets()
' Don't show confirmation window
Application.DisplayAlerts = False

Set wbk1 = ThisWorkbook
For Each sh In wbk1.Sheets
If sh.Name = "ShippingConfirmation" Then
Set wbk2 = Workbooks.Add
sh.Copy Before:=wbk2.Sheets(1)
wbk2.Sheets(sh.Name).UsedRange.Value = wbk2.Sheets(sh.Name).UsedRange.Value
wbk2.SaveAs Filename:=ThisWorkbook.Path & "/" & sh.Name & "txt"
wbk2.Close
End If
Next sh
' Allow confirmation windows to appear as normal
Application.DisplayAlerts = True

End Sub

 

[Micron]

Do you know that you can Save As csv from the File menu? Tons of code examples for this though. Here's one

Excel: macro to export worksheet as CSV file without leaving my current Excel sheet

There are a lot of questions here to create a macro to save a worksheet as a CSV file. All the answers use the SaveAs, like this one from SuperUser. They basically say to create a VBA function like...

stackoverflow.com

HTH.

 

[dpnab]

Do you know that you can Save As csv from the File menu? Tons of code examples for this though. Here's one

Excel: macro to export worksheet as CSV file without leaving my current Excel sheet

There are a lot of questions here to create a macro to save a worksheet as a CSV file. All the answers use the SaveAs, like this one from SuperUser. They basically say to create a VBA function like...

stackoverflow.com

HTH.

Thanks for this. I will try a csv but the requirement is for it to be a tab delimited file, so a csv won't work?

 

[Jimmypop]

Have you tried...

Sub Export_Sheets()
    ' Don't show confirmation window
    Application.DisplayAlerts = FALSE
    Set wbk1 = ThisWorkbook
    For Each sh In wbk1.Sheets
        If sh.Name = "ShippingConfirmation" Then
            Set wbk2 = Workbooks.Add
            sh.Copy Before:=wbk2.Sheets(1)
            wbk2.Sheets(sh.Name).UsedRange.Value = wbk2.Sheets(sh.Name).UsedRange.Value
            wbk2.SaveAs Filename:=ThisWorkbook.Path & "/" & sh.Name & "txt", FileFormat _
                        :=xlText, CreateBackup:=False
            wbk2.Close
        End If
    Next sh
    ' Allow confirmation windows to appear as normal
    Application.DisplayAlerts = TRUE
End Sub

 

[dpnab]

Have you tried...

Sub Export_Sheets()
    ' Don't show confirmation window
    Application.DisplayAlerts = FALSE
    Set wbk1 = ThisWorkbook
    For Each sh In wbk1.Sheets
        If sh.Name = "ShippingConfirmation" Then
            Set wbk2 = Workbooks.Add
            sh.Copy Before:=wbk2.Sheets(1)
            wbk2.Sheets(sh.Name).UsedRange.Value = wbk2.Sheets(sh.Name).UsedRange.Value
            wbk2.SaveAs Filename:=ThisWorkbook.Path & "/" & sh.Name & "txt", FileFormat _
                        :=xlText, CreateBackup:=False
            wbk2.Close
        End If
    Next sh
    ' Allow confirmation windows to appear as normal
    Application.DisplayAlerts = TRUE
End Sub

That does it thanks! Any idea what I can add to only copy/paste the values of cells that have a value? I have some formulas that are IF statements that return a blank in certain scenarios. So if it's blank (the cell still has a formula) it shows up as blank lines in the txt file.

 

[Micron]

For future consideration, there is also an option to save as tab delimited text file. Lots of other options in fact.

 

[Jimmypop]

That does it thanks! Any idea what I can add to only copy/paste the values of cells that have a value? I have some formulas that are IF statements that return a blank in certain scenarios. So if it's blank (the cell still has a formula) it shows up as blank lines in the txt file.

Not sure about this one.. Maybe someone else can have a go?