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
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