none
Converting Excel file to a tab demited text file RRS feed

  • Question

  • I need to convert Excel workbook contains many worksheets into many many tab delimited text files . each tab delimited text file generated is one sheets of the workbook excel file How can I accomplish this task using C# ?

    I tried vbscript with the save as method with the value 20 and 21 but with errors ??

    Sunday, February 8, 2015 9:52 PM

Answers

  • I'm sure you can use C# for tis task, but that seems overly complicated. 

    #1)  Use this ton convert 1 large Workbook to many small Workbooks.

    http://www.rondebruin.nl/win/s3/win007.htm

    #2)  Use this to convert each small workbook to a CSV file...which is essentially a Text file.

    Sub SaveToCSVs()
        Dim fDir As String
        Dim wB As Workbook
        Dim wS As Worksheet
        Dim fPath As String
        Dim sPath As String
        fPath = "C:\Users\rshuell\Desktop\My_Excel_Files\"
        sPath = "C:\Users\rshuell\Desktop\My_Excel_Files\"
        fDir = Dir(fPath)
        Do While (fDir <> "")
        If Right(fDir, 4) = ".xls" Or Right(fDir, 5) = ".xlsx" Then
        On Error Resume Next
        Set wB = Workbooks.Open(fPath & fDir)
        For Each wS In wB.Sheets
        wS.SaveAs sPath & wS.Name, xlCSV
        Next wS
        wB.Close False
        Set wB = Nothing
        End If
        fDir = Dir
        On Error GoTo 0
        Loop
    End Sub
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, February 20, 2015 7:00 PM

All replies

  • I need to convert Excel workbook contains many worksheets into many many tab delimited text files . each tab delimited text file generated is one sheets of the workbook excel file How can I accomplish this task using C# ?

    I tried vbscript with the save as method with the value 20 and 21 but with errors ??

    The following is a vba subroutine that will do what you need.

    Sub SaveAllSheets2Text()
        Dim sh As Worksheet
        For Each sh In ThisWorkbook.Worksheets
            sh.Copy
            ActiveWorkbook.SaveAs "C:\Users\me\Documents\" & sh.Name, xlTextWindows ' change to right path
            ActiveWorkbook.Close
        Next sh
    End Sub
    The trick was to copy each worksheet first to a new workbook and save as text and close the file. Please mark my reply as an answer if it worked for.

    • Edited by John Martel Monday, February 9, 2015 1:33 AM
    • Proposed as answer by John Martel Monday, February 9, 2015 1:33 AM
    Monday, February 9, 2015 1:30 AM
  • I tried your code with failure :

    Dim oBook 
    Dim Sheet as Worksheet
    Dim Folder
    Dim FileName
    Const xlCurrentPlatformText = -4158
    
    Set objArgs = WScript.Arguments
    
    FullName = objArgs(I)
    'FileName = Left(objArgs(I), InstrRev(objArgs(I), ".") )
    
    Set oBook = WScript.CreateObject("Excel.application")
    oBook.Workbooks.Open(Wscript.Arguments.Item(0) &"\" &Wscript.Arguments.Item(1))
    
    FileName=(Wscript.Arguments.Item(1))
    Folder=(Wscript.Arguments.Item(0))
          MsgBox FileName
    
    oBook.application.visible=false
    oBook.application.displayalerts=false
    
    For Each Sheet In oBook.Worksheets
    Sheet.Copy
    oBook.ActiveWorkbook.SaveAs Folder &"\" &FileName &"_" &Sheet.Name , xlTextWindows ' change to right path
    oBook.ActiveWorkbook.Close
    Next 
    
    'oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Folder &"\" &FileName &"_" &Sheet.Name &".txt", xlTextWindows
    'ActiveWorkbook.Close
    'MsgBox FileName+Sheet.Name
    'Next 
    'MsgBox "Finishing creations of "+FileName+".txt"
    
    oBook.Application.Quit
        oBook.Quit   
        Set oBook = Nothing
        set oBookOpen = Nothing
    

    Wednesday, February 11, 2015 8:44 PM
  • I need to transfer Excel workbook has many sheets to tab delimited text file . 

    Here is the code with failure . Failure that the resulted files are not the same as choice of save as from the inside Excel .

    Dim oBook 
    Dim Sheet as Worksheet
    Dim Folder
    Dim FileName
    Const xlCurrentPlatformText = -4158
    
    Set objArgs = WScript.Arguments
    
    FullName = objArgs(I)
    'FileName = Left(objArgs(I), InstrRev(objArgs(I), ".") )
    
    Set oBook = WScript.CreateObject("Excel.application")
    oBook.Workbooks.Open(Wscript.Arguments.Item(0) &"\" &Wscript.Arguments.Item(1))
    
    FileName=(Wscript.Arguments.Item(1))
    Folder=(Wscript.Arguments.Item(0))
          MsgBox FileName
    
    oBook.application.visible=false
    oBook.application.displayalerts=false
    
    For Each Sheet In oBook.Worksheets
    Sheet.Copy
    oBook.ActiveWorkbook.SaveAs Folder &"\" &FileName &"_" &Sheet.Name , xlTextWindows ' change to right path
    oBook.ActiveWorkbook.Close
    Next 
    
    'oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Folder &"\" &FileName &"_" &Sheet.Name &".txt", xlTextWindows
    'ActiveWorkbook.Close
    'MsgBox FileName+Sheet.Name
    'Next 
    'MsgBox "Finishing creations of "+FileName+".txt"
    
    oBook.Application.Quit
        oBook.Quit   
        Set oBook = Nothing
        set oBookOpen = Nothing

    • Merged by George123345 Thursday, February 12, 2015 5:13 AM same topic
    Wednesday, February 11, 2015 8:50 PM
  • I tried your code with failure :

    Dim oBook 
    Dim Sheet as Worksheet
    Dim Folder
    Dim FileName
    Const xlCurrentPlatformText = -4158
    
    Set objArgs = WScript.Arguments
    
    FullName = objArgs(I)
    'FileName = Left(objArgs(I), InstrRev(objArgs(I), ".") )
    
    Set oBook = WScript.CreateObject("Excel.application")
    oBook.Workbooks.Open(Wscript.Arguments.Item(0) &"\" &Wscript.Arguments.Item(1))
    
    FileName=(Wscript.Arguments.Item(1))
    Folder=(Wscript.Arguments.Item(0))
          MsgBox FileName
    
    oBook.application.visible=false
    oBook.application.displayalerts=false
    
    For Each Sheet In oBook.Worksheets
    Sheet.Copy
    oBook.ActiveWorkbook.SaveAs Folder &"\" &FileName &"_" &Sheet.Name , xlTextWindows ' change to right path
    oBook.ActiveWorkbook.Close
    Next 
    
    'oBook.ActiveWorkbook.Worksheets(Sheet.Name).SaveAs Folder &"\" &FileName &"_" &Sheet.Name &".txt", xlTextWindows
    'ActiveWorkbook.Close
    'MsgBox FileName+Sheet.Name
    'Next 
    'MsgBox "Finishing creations of "+FileName+".txt"
    
    oBook.Application.Quit
        oBook.Quit   
        Set oBook = Nothing
        set oBookOpen = Nothing


    The code you have in your reply is not the code that I had provided. Did you try the code I had in my original post, if so where did the code fail, what error message did you get.
    Wednesday, February 11, 2015 9:16 PM
  • I tried you code as below with slightly updates because original does not with as worksheet and next sh .

    I tried as wscript D:\shared\programs\vbscript\Audi\Excel2\wscript audi_xls_txt6.vbs

    Without any Text files generated ??

    Sub SaveAllSheets2Text()
        Dim sh 
        Set ThisWorkbook=WScript.CreateObject("Excel.Application")
        ThisWorkbook.Workbooks.Open("D:\shared\programs\vbscript\Audi\Excel2\GL-01 2015.xls")
        For Each sh In ThisWorkbook.Worksheets
            sh.Copy
            ActiveWorkbook.SaveAs "D:\shared\programs\vbscript\Audi\Excel2\" & sh.Name, xlTextWindows ' change to right path
            ActiveWorkbook.Close
        Next 
    End Sub



    • Edited by ehabaziz2001 Saturday, February 14, 2015 1:09 PM
    Saturday, February 14, 2015 9:06 AM
  • I'm sure you can use C# for tis task, but that seems overly complicated. 

    #1)  Use this ton convert 1 large Workbook to many small Workbooks.

    http://www.rondebruin.nl/win/s3/win007.htm

    #2)  Use this to convert each small workbook to a CSV file...which is essentially a Text file.

    Sub SaveToCSVs()
        Dim fDir As String
        Dim wB As Workbook
        Dim wS As Worksheet
        Dim fPath As String
        Dim sPath As String
        fPath = "C:\Users\rshuell\Desktop\My_Excel_Files\"
        sPath = "C:\Users\rshuell\Desktop\My_Excel_Files\"
        fDir = Dir(fPath)
        Do While (fDir <> "")
        If Right(fDir, 4) = ".xls" Or Right(fDir, 5) = ".xlsx" Then
        On Error Resume Next
        Set wB = Workbooks.Open(fPath & fDir)
        For Each wS In wB.Sheets
        wS.SaveAs sPath & wS.Name, xlCSV
        Next wS
        wB.Close False
        Set wB = Nothing
        End If
        fDir = Dir
        On Error GoTo 0
        Loop
    End Sub
    


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Friday, February 20, 2015 7:00 PM