CODEWITHSUNDEEP
vbaexcelexcel-2007
user955289
user955289

Reputation: 171

Need to modify this export macro for text tab delimited output

I found a script on here that will export each worksheet in a file to a .csv file, but I need to adjust it to export the sheets as text tab delimited files instead. I tried to modify it, but it is just exporting as text with no delimiter. Here is the original code:

Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim wsSheet As Worksheet
Dim nFileNum As Integer
Dim csvPath As String


Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _
"Export To Text File")
'csvPath = InputBox("Enter the full path to export CSV files to: ")

csvPath = GetFolderName("Choose the folder to export CSV files to:")
If csvPath = "" Then
    MsgBox ("You didn't choose an export directory. Nothing will be exported.")
    Exit Sub
End If

For Each wsSheet In Worksheets
wsSheet.Activate
nFileNum = FreeFile
Open csvPath & "\" & _
  wsSheet.Name & ".csv" For Output As #nFileNum
ExportToTextFile CStr(nFileNum), Sep, False
Close nFileNum
Next wsSheet

End Sub

And here is how I have modified it:

Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim wsSheet As Worksheet
Dim nFileNum As Integer
Dim txtPath As String


'Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _
'"Export To Text File")
'csvPath = InputBox("Enter the full path to export CSV files to: ")

txtPath = GetFolderName("Choose the folder to export TXT files to:")
If txtPath = "" Then
    MsgBox ("You didn't choose an export directory. Nothing will be exported.")
    Exit Sub
End If

For Each wsSheet In Worksheets
wsSheet.Activate
nFileNum = FreeFile
Open txtPath & "\" & _
  wsSheet.Name & ".txt" For Output As #nFileNum
ExportToTextFile CStr(nFileNum), Sep, False
Close nFileNum
Next wsSheet

End Sub

Thanks in advance!

Upvotes: 0

Views: 3335

Answers (1)

markblandford
markblandford

Reputation: 3193

It looks as if you're not setting Sep to be anything whereas it looks as if it should be your delimiter. Use the following before your For Each.. loop.

Sep = vbTab

Or

Sep = Chr(9)

Upvotes: 2

Related Questions