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

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
    Set objSheet = objExcel.ActiveWorkbook.Worksheets(z)
    strSheetName =

    LastRow = objSheet.UsedRange.Rows.Count + 2
    LastColumn = objSheet.UsedRange.Columns.Count   
    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
        ts.write Chr(13) & Chr(10)
  z= z-1

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


Popular posts from this blog

Trim / Remove spaces in Xpath?

RPA - Blue Prism, OpenSpan, Automation Anywhere vs UIPath