Exporting an Excel file to a text file with format .txt

[lucpian]

Hi All,

I am trying to write a VBA code that will open and export an excel file to a text file format with an extension of .txt. I wrote the following code, but for some reason which I cannot figure out, it is not working. Here is the code:-

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DoTheExport
' This prompts the user for the FileName and the separtor
' character and then calls the ExportToTextFile procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DoTheExport()
Dim FileName As Variant
Dim Sep As String
Dim File As String


''''''''''''''''''''''''''
' user opens a filename, for it to be saved as
''''''''''''''''''''''''''
FileName = Application.GetOpenFilename(FileFilter:="Excel Files(*.xls), *.xls", _
Title:="Select the files to export", MultiSelect:=True)

If TypeName(File) = "Boolean" Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If

'Turn off screen updating
Application.ScreenUpdating = False
' For i = LBound(File) To UBound(File)
' process(File) WhichFile:=Cstr(File(i)), WhichBook:=Book
'Next i

Application.DisplayAlerts = True
'Restore screen updating
Application.ScreenUpdating = True





''''''''''''''''''''''''''
' user gives a filename, for it to be saved as
''''''''''''''''''''''''''
'
If FileName = True Then
''''''''''''''''''''''''''
' user cancelled, get out
FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt") ''''''''''''''''''''''''''
Exit Sub
End If

If FileName = False Then
''''''''''''''''''''''''''
' user cancelled, get out

Exit Sub
End If
'Sep = Application.InputBox("Enter a separator character.", Type:=2)
' If Sep = vbNullString Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
' Exit Sub
'End If
Debug.Print "FileName: " & FileName, "Separator: "; ","
ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
SelectionOnly:=False, AppendData:=True
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END DoTheExport
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Please, can someone help me out, because I have spent the whole of today trying to figure out what is wrong with the code.

Thanks

Lucpian

[lucpian]

Hi All,

I am trying to write a VBA code that will open and export an excel file to a text file format with an extension of .txt. I wrote the following code, but for some reason which I cannot figure out, it is not working. Here is the code:-

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DoTheExport
' This prompts the user for the FileName and the separtor
' character and then calls the ExportToTextFile procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DoTheExport()
Dim FileName As Variant
Dim Sep As String
Dim File As String


''''''''''''''''''''''''''
' user opens a filename, for it to be saved as
''''''''''''''''''''''''''
FileName = Application.GetOpenFilename(FileFilter:="Excel Files(*.xls), *.xls", _
Title:="Select the files to export", MultiSelect:=True)

If TypeName(File) = "Boolean" Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If

'Turn off screen updating
Application.ScreenUpdating = False
' For i = LBound(File) To UBound(File)
' process(File) WhichFile:=Cstr(File(i)), WhichBook:=Book
'Next i

Application.DisplayAlerts = True
'Restore screen updating
Application.ScreenUpdating = True





''''''''''''''''''''''''''
' user gives a filename, for it to be saved as
''''''''''''''''''''''''''
'
If FileName = True Then
''''''''''''''''''''''''''
' user cancelled, get out
FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt") ''''''''''''''''''''''''''
Exit Sub
End If

If FileName = False Then
''''''''''''''''''''''''''
' user cancelled, get out

Exit Sub
End If
'Sep = Application.InputBox("Enter a separator character.", Type:=2)
' If Sep = vbNullString Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
' Exit Sub
'End If
Debug.Print "FileName: " & FileName, "Separator: "; ","
ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
SelectionOnly:=False, AppendData:=True
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END DoTheExport
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Please, can someone help me out, because I have spent the whole of today trying to figure out what is wrong with the code.

Thanks

Lucpian

[RonMcK3]

Lucpian,

Since you want to save the file as .txt, how about using the MS Excel SaveAs dialog? (See 'MS' KB 139732)

[vba]Application.Dialogs(xlDialogSaveAs).Show[/vba]
This gives you access to tab and space delimited as .txt and comma delimited as .csv files. You can use the explorer window to name your file, set your path/pick a folder, and select your file type.

HTH,

Ron McKenzie
Windermere, FL (in the shadow of a wee rodent)

[lucpian]

Hi Ron,

Thanks for your help. I do know that, but this particular project needs vba code that will be linked to a button, which will simplified it all for the user.

Lucpian

[RonMcK]

Lucpian,

How about using something like the following and assigning it to your button?

[vba]Sub SaveIt()
Application.Dialogs(xlDialogSaveAs).Show
End Sub[/vba]

Is the challenge that your users what to use delimiters other than space, comma, or tab? Or, do you want to limit where they can save the files?

Ron
Orlando

[lucpian]

Ron,

Why I had to write the above vba code that is giving me errors was because the exported file needs to be comma-delimited text file and you do not have such option if I use the saved as dialog code you have just suggested.

Thanks

Lucpian

[RonMcK]

Lucpian,

Please take another peek at the dialog. Beneath the explorer pane is a drop down list of supported file types. Looking at it on my Mac, (XL 2004) here at work, I see 30 formats, 4 common ones including CSV, and 26 specialty ones, including CSV (Windows) and CSV(Mac); I saw much the same on my PC at home (XL 2002/3).

Thanks,

Ron
Orlando, FL