PDA

View Full Version : Exporting an Excel file to a text file with format .txt



lucpian
02-13-2008, 03:12 PM
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
02-13-2008, 03:12 PM
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
02-13-2008, 10:49 PM
Lucpian,

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

Application.Dialogs(xlDialogSaveAs).Show
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
02-14-2008, 08:01 AM
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
02-14-2008, 08:14 AM
Lucpian,

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

Sub SaveIt()
Application.Dialogs(xlDialogSaveAs).Show
End Sub

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
02-14-2008, 09:40 AM
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
02-14-2008, 09:49 AM
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