Convert Excel to Text file


I was trying to convert an excel files worksheets to a single text file , there was lot of reusable code on the web for converting text to excel but not viceversa. So for all of you who want to do what  I did, here is the simple and easy code to convert excel to text file based on the rows and columns of the excel file:

'Prompts for accepting user input
strViewPath = Trim (InputBox ("Plz enter the path for the excel file",,"C:\temp\"))
strTest = Trim (InputBox ("Plz enter the text file name",,"sample"))
       
If Right (strViewPath, 1) <> "\" Then
   strViewPath = strViewPath & "\"   
End If       

strTestName = strTest
strTextFilePath = strViewPath
   
'Assign the values for the excel and text file that needs to be converted
TestToConvert = strViewPath + strTest + ".xls"
TextFile =strTextFilePath  + strTestName + ".txt"
   
'Create the excel object
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False

'Open the excel file for conversion
oExcel.DisplayAlerts = False
oExcel.Workbooks.Open TestToConvert, True
'Call the text streamer function that will convert the file
TextStreamer TextFile, oExcel
 
'Exit the Excel file
oExcel.Quit

Private Sub TextStreamer(TextFileName, objExcel)

'Declare constants for reading,writing and appending to a text file
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
   
Dim fs, f, ts, x, y, LastRow, LastColumn, c, objSheet, shts()
'Create the file system object for text file editing
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile TextFileName
       
Set f = fs.GetFile(TextFileName)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)
   
'Get the number of worksheets in the source excel file
intNoOfSheets = objExcel.Worksheets.count
z = intNoOfSheets
   
'Traverse through every sheet that needs to be converted
For i = 1 to intNoOfSheets
       
 'Activate the first worksheet
    objExcel.Worksheets(z).Activate
    objExcel.Worksheets(z).Select
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(z)
    
    strSheetName = objsheet.name
    objSheet.Cells(1).Select

    LastRow = objSheet.UsedRange.Rows.Count + 2
    LastColumn = objSheet.UsedRange.Columns.Count   
                   
    objSheet.Cells(1).Select
                   
    ts.write "["&strSheetName&"]"
    ts.write Chr(13) & Chr(10)
           
    'Loop through the rows and columns in the excel worksheet and write the data to the text file       
    For x = 0 To LastRow
        For y = 0 To LastColumn -1
            If objExcel.ActiveCell.Offset(x, y).Value <> "" then
                ts.write (objExcel.ActiveCell.Offset(x, y).Value)
                'ts.write Chr(9)   
            End If
        Next
        ts.write Chr(13) & Chr(10)
    Next               
  z= z-1

Next
       
'Close the excel file test streamer
ts.Close
msgbox "Conversion Complete!"
End Sub

Comments

Popular posts from this blog

Software Testing @ Microsoft

Trim / Remove spaces in Xpath?