CODEWITHSUNDEEP
excelvbscript
CarlJohn
CarlJohn

Reputation: 727

convert excel file to txt file (without carriage returns)

i want to convert excel files into tab limited txt files(without carriage returns). Currently i'm using the script(found in this forum) which converts mass excel files into .txt files.

the script is

    ' @file:    xl2tab.vbs
' @author:  stephen brown - [email protected]
' @date:    2009-Dec-10
' 
' @description: mass convert excel files to tab-delimited files
'
' @usage:   place in top-level directory where excel files are contained and double-click.
'           script will recursively access all subdirectories and convert each excel file to
'           tab delimited file. All output will be in "output" folder, which retains structure
'           of original directories

Dim saveDirBase

set fso = CreateObject("Scripting.FileSystemObject")
set shell = CreateObject("WScript.Shell")
set objExcel = CreateObject("Excel.Application")

set top = fso.GetFolder(shell.CurrentDirectory)
saveDirBase = top & "\" & "output"

Sub TraverseFolders(path)
    set folder = fso.GetFolder(path)

    XL2Tab(folder)

    For each item in folder.SubFolders
        If item.Path <> saveDirBase Then
            Call TraverseFolders(item.Path)
        End If
    Next

    set folder = Nothing
End Sub

Sub XL2Tab(folder)
    Dim saveDir
    set files = folder.Files

    If folder.Name <> top.Name Then
        saveDir = saveDirBase & "\" & folder.Name
    Else
        saveDir = saveDirBase
    End If

    If fso.FolderExists(saveDir) = False Then
        fso.CreateFolder(saveDir)   
    End If

    For each file In files
        If file.Name <> Wscript.ScriptName Then
            objExcel.Application.DisplayAlerts = False
            Set objWorkbook = objExcel.Workbooks.open(folder.Path & "\" & file.Name)   
            objWorkbook.SaveAs saveDir & "\" & file.Name & ".txt", -4158    
            objWorkbook.close
            objExcel.Application.DisplayAlerts = True
        End If
    Next
End Sub

If fso.FolderExists(saveDirBase) = False Then
    fso.CreateFolder(saveDirBase)   
End If

Call TraverseFolders(top)

Before converting i want to remove carriage return in every excel file.

Please guide me anyone...!

Upvotes: 1

Views: 3776

Answers (1)

Sudheej
Sudheej

Reputation: 2019

Hi If you are trying to remove carriage returns after converting the file add the below procedure to code

 sub RemoveCarriage(FileN)
 Const ForReading = 1
 Const ForWriting = 2
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objFile = objFSO.OpenTextFile(FileN, ForReading)
 strText = objFile.ReadAll
 objFile.Close
 strNewText = Replace(strText, chr(013) & chr(010), "") 
 ' chr(010) = line feed chr(013) = carriage return
 Set objFile = objFSO.OpenTextFile(FileN, ForWriting)
 objFile.WriteLine strNewText
 objFile.Close 
 End sub

Call the module inside forloop of your procedure just after closing the workbook objWorkbook.close

 RemoveCarriage(file.Name & ".txt")

Upvotes: 1

Related Questions