PDA

View Full Version : Save file as TXT or CSV



av8tordude
08-02-2010, 08:30 AM
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
08-02-2010, 02:35 PM
Wondering if anyone can help?

Kenneth Hobs
08-02-2010, 06:44 PM
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.


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