Save file as TXT or CSV

[av8tordude]

When I enter a file name with either a .csv extension or .txt extension I would like to edit the code below to automatically recognise the extension and save it as either a (.txt) file or (.csv) file.

Part of the process requires the user to select either CSV or TXT option and then click export. I would like to eliminate this step. I believe the code below identifies which option has been selected. Can someone assist me. Thank you

If Me.obTAB.Value = True Then
        Extn = ".txt"
        ff = xlText
    Else
        Extn = ".csv"
        ff = xlCSV
    End If

The entire export code is...

Private Sub cmdLExport_Click()
Dim fName As String, Extn As String, ff, rng As Range
Dim aWb As Workbook, wb As Workbook, r As Long
    Set aWb = ActiveWorkbook
    With aWb.Sheets("Logbook")
        r = Application.Max(9, .Range("a10010").End(xlUp).Row)
        Set rng = .Range("a9:ab" & r)
    End With
    If Me.obTAB.Value = True Then
        Extn = ".txt"
        ff = xlText
    Else
        Extn = ".csv"
        ff = xlCSV
    End If
 
    If Right$(FilePath, 1) <> "\" Then FilePath = FilePath & "\"
 
    fName = Left$(txtFile, InStrRev(txtFile, ".") - 1) & Extn
    With Application
        .DisplayAlerts = 0
        .ScreenUpdating = 0
        .EnableEvents = 0
    End With
    On Error GoTo Xit
 
    Set wb = Workbooks.Add
    rng.Copy wb.Sheets(1).Range("a1")
    wb.SaveAs FileName:=fName, FileFormat:=ff
    wb.Close False
    Set wb = Nothing
    Set aWb = Nothing
Xit:
    If Err.Number <> 0 Then
        MsgBox Err.Number & " " & Err.Description
        Err.Clear
    End If
    With Application
        .DisplayAlerts = 1
        .ScreenUpdating = 1
        .EnableEvents = 1
    End With
    MsgBox UCase(Mid$(Extn, 2)) & " Backup File Successfully Created.", vbInformation, "FlightLog - Professional Edition"
    Unload Me
Exit Sub
End Sub

[av8tordude]

Wondering if anyone can help?

[Kenneth Hobs]

What seems to be the problem? You should understand that it won't have a header row. You will need to code for that if you want it.

This is just your code with a few of the variables defined so that I could test it.

[VBA]
Private Sub cmdLExport_Click()
Dim fName As String, Extn As String, ff, rng As Range
Dim aWb As Workbook, wb As Workbook, r As Long

Dim Filepath As String, txtFile As String
Filepath = ThisWorkbook.Path
txtFile = ThisWorkbook.Name

Set aWb = ActiveWorkbook
With aWb.Sheets("Logbook")
r = Application.Max(9, .Range("a10010").End(xlUp).Row)
Set rng = .Range("a9:ab" & r)
End With
If Me.obTab.Value = True Then
Extn = ".txt"
ff = xlText
Else
Extn = ".csv"
ff = xlCSV
End If

If Right$(Filepath, 1) <> "\" Then Filepath = Filepath & "\"

fName = Left$(txtFile, InStrRev(txtFile, ".") - 1) & Extn
With Application
.DisplayAlerts = 0
.ScreenUpdating = 0
.EnableEvents = 0
End With
On Error GoTo Xit

Set wb = Workbooks.Add
rng.Copy wb.Sheets(1).Range("a1")
wb.SaveAs Filename:=fName, FileFormat:=ff
wb.Close False
Set wb = Nothing
Set aWb = Nothing
Xit:
If Err.Number <> 0 Then
MsgBox Err.Number & " " & Err.Description
Err.Clear
End If
With Application
.DisplayAlerts = 1
.ScreenUpdating = 1
.EnableEvents = 1
End With
MsgBox UCase(Mid$(Extn, 2)) & " Backup File Successfully Created.", vbInformation, "FlightLog - Professional Edition"
Unload Me
Exit Sub
End Sub[/VBA]