• Home
 · 
• Files/File Controls/Input/Output
 · 
• Convert excel to text from command line

Convert excel to text from command line

Convert excel to text from command line

API Declarations


Dim rs As New ADODB.Recordset

Dim ts As TextStream, fill As File
Dim fso As New FileSystemObject
Dim app As String
Dim i As Integer
Dim sFields As String
Dim sData As String
Dim myPath As String
Dim fld As ADODB.Field
Dim fldCat As String
Dim iSpace As Integer
Dim sSourceData As String
Dim currLoc, cnt As Integer
Dim StringLength As Integer
Dim tmpChar As String




Rate Convert excel to text from command line

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

Dim a, b, s, y As Integer

On Error GoTo Form_Load_Error

'To read from the dos prompt
    catch1 = Command$
   'MsgBox catch1
   cnt = 0
  'catch1 = "c:\test.xls Sheet1"
   

    StringLength = Len(catch1)
    For currLoc = 1 To StringLength
        
        tmpChar = Mid(catch1, currLoc, 1)
        
'        If InStr(" ", tmpChar) Then
        If tmpChar = " " Then
            ' Replace with a space
             
             'Mid(catch1, currLoc, 1) = " "
             cnt = cnt + 1
             
         Else
             If cnt >= 1 Then
               app = app & " "
               cnt = 0
             End If
             
             app = app & tmpChar
        End If
        
        
    Next
    
    catch = app

 
 
 

 
   a = InStr(1, Trim(catch), "xls")
   b = InStr(1, Trim(catch), "nobypass")
   s = InStr(1, Trim(catch), " ")
   y = InStr(s + 1, Trim(catch), " ")
   'slas = InStr(1, Trim(catch), "\")
   
   
 
 If Val(s) = 0 And Val(y) = 0 Then
  If a > 0 Then
   nm = catch
   fl = Mid$(nm, 1, (Len(nm) - 4)) & ".txt"
   Else
    nm = catch & ".xls"
    fl = catch & ".txt"
  End If
   sh = "Sheet1$"
   
End If

If Val(y) = 0 And Val(s) <> 0 Then
 If a > 0 Then
  nm = Mid$(catch, 1, s - 1)
  fl = Mid$(nm, 1, (Len(nm) - 4)) & ".txt"
 Else
  nm = Mid$(catch, 1, s - 1) & ".xls"
  fl = Mid$(nm, 1, s - 1) & ".txt"
 End If
  sh = Mid$(catch, s + 1, Len(catch)) & "$"
  
End If



If Val(s) <> 0 And Val(y) <> 0 Then
 If a > 0 Then
  nm = Mid$(catch, 1, s - 1)
 Else
  nm = Mid$(catch, 1, s - 1) & ".xls"
 End If
  sh = Mid$(catch, Val(s + 1), Val(y - s - 1)) & "$"
  If Val(b) = 0 Then
      fl = Mid$(catch, Val(y + 1), Len(catch))
   Else
      fl = Mid$(catch, Val(y + 1), Val(b - y - 1))
  End If
End If


   
 Set rs = New ADODB.Recordset
 Set cnn = New ADODB.Connection



  
    
       'myPath = "C:\" & nm
        myPath = nm

      
    'If rs.State = 1 Then rs.Close
      cnn.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & myPath & ";ReadOnly=1"
      rs.Open "SELECT * FROM [" & sh & "]", cnn
    
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set ts = fs.CreateTextFile(fl, True)
   
   
  Do While Not rs.EOF
  i = 0
        For Each fld In rs.Fields
        
        If Val(b) > 0 Then
          fldCat = fldCat & IIf(IsNull(fld), "|", fld & "|")
        Else
          If IsNull(fld) = True Then
            Else
             'fldCat = fldCat & fld & "|"
             If i = 0 Then
               fldCat = fldCat & fld
              Else
              fldCat = fldCat & "|" & fld
             End If
             i = i + 1
           End If
        End If
        Next
          
         If i <> 0 Then
            ts.WriteLine (Trim(fldCat))
         End If
        
          fldCat = ""
          rs.MoveNext
  Loop
 
   
  ts.Close
  
'  Open " & x & " For Input As #1
'
'  Close 1#
  
  

   On Error Resume Next
   If Not rs Is Nothing Then
     rs.Close
    
   
     
   
   Set rs = Nothing
   End If
    
    If Not cnn Is Nothing Then
      cnn.Close
    Set cnn = Nothing
    End If
   
   On Error GoTo 0

   


'Form_Load_Exit:
   Exit Sub

Form_Load_Error:

   MsgBox "Usage   : Excel-File [Sheet Output nobypass] " & vbCrLf & _
          "Example: cett xyz.xls sheet1 out.txt                 -create out.txt from xyz.xls sheet1 sheet" & vbCrLf & Space(13) & _
          "  cett xyz.xls jan jan.txt                       -create jan.txt from xyz.xls jan sheet " & vbCrLf & _
          "               cett xyz                                            -create xyz.txt from xyz.xls sheet1 sheet " & vbCrLf & _
          "               cett xyz.xls exp exp.txt nobypass     -create exp.txt from xyz.xls exp sheet" & vbCrLf & _
          "                                                                        without bypassing the blank lines ", , "CETT : Convert Excel To Text"


End Sub