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

Post a Comment

Popular posts from this blog

Software Testing @ Microsoft

Image
In continuation from my previous  series of posts  targeting testing in top few companies from the technology space. After  Software Testing @Facebook  and @Google  we shall now look at Microsoft: Testing@Microsoft Software Development Engineers in Test (SDETs) are usually just called Test and sometimes Software Testing. SDETs are responsible for maintaining high testing and qualityassurance standards for all Microsoft products. Software Development Engineers (SDEs) are often referred to as Software Development. SDEs write the code that drives Microsoft products and upgrades. Very few teams in Microsoft are still using SDETs to do significant amount of manual testing. Manual testing is mainly outsourced. Actually, as early as in 2004, for example, most of the manual testing of MSN Explorer was outsourced. Think about it this way: it just doesn't make sense for test manager to spend their headcount (SDETs) on something can be easily outsourced.  Until 2005, Microso
Read more

Trim / Remove spaces in Xpath?

Image
If you are wondering how you can trim the spaces in your xpath, this is how: The normalize-space function strips leading and trailing white-space from a string, replaces sequences of white-space characters by a single space, and returns the resulting string. Example : Which one is better? //td[starts-with(normalize-space(),'Text to Trim')] //td[starts-with(normalize-space(text()),' Text to Trim ')] The second option is better as it targets at the specific nodes More string functions in XPATH: Functions Description starts-with( string1 ,  string2 ) Returns true if the first string starts with the second string. contains( string1 ,  string2 ) Returns true if the first string contains the second string. substring( string offset length ) Returns a section of the string. The section starts at  offset (which is a number), and is as long as the value provided at  length. substring-before( string1 ,  string2 ) Returns the part of  string1  up unti
Read more