Need to Save Multiple Excel to text tab delimited File

[venugosr]

Hi,

Please be informed that, daily i'll get minimum 20 to 30 excel workbook.
Each workbook, i need to open and save as Text(Tab Delimited) Only the first sheet. Its consuming more time.
So, if it is 30 files, then I need to do the same.

Could someone help to automate this?

Kindly let me know if you need any details.

 

[venugosr]

I have the below code, but it will save all the sheets in the workbook, which i dont want.
I want the first sheet only to save, but for all the excel files in the folder.

Sub SaveSheets()
Dim ws As Worksheet
Dim book As Workbook
Dim count As Integer
For Each ws In ThisWorkbook.Worksheets
  Set book = Workbooks.Add
  count = book.Worksheets.count
  ws.Copy book.Worksheets(1)
  
  Application.DisplayAlerts = False
  book.Worksheets(1).SaveAs "G:\Sri\" & ws.Name & ".txt", xlTextWindows
  Application.DisplayAlerts = True
  book.Close False
  Set book = Nothing
Next
MsgBox "Files successfully created."
End Sub

 

[John_w]

You need a Dir function loop. Try this, changing the folder path containing the workbooks where indicated.

Public Sub Save_Workbooks_As_Tabbed()

    Dim folderPath As String
    Dim fileName As String
    Dim p As Long
    
    folderPath = "C:\path\to\folder\"  'CHANGE THIS STRING
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    Application.ScreenUpdating = False
    
    fileName = Dir(folderPath & "*.xls*")
    Do While fileName <> vbNullString
        Workbooks.Open folderPath & fileName
        p = InStrRev(fileName, ".") - 1
        ActiveWorkbook.SaveAs fileName:=folderPath & Left(fileName, p) & ".txt", FileFormat:=xlText, CreateBackup:=False
        ActiveWorkbook.Close False
        fileName = Dir
    Loop

    Application.ScreenUpdating = True
    
    MsgBox "Done"

End Sub