xls and xlsx to text macro but for one sheet in specific only

June 16, 2022

I have this code to process from a directory all the xls and xlsx and convert them to txt delimited columns and works fine:

Public Sub Save_Workbooks_As_Tabbed()

    Dim folderPath As String
    Dim fileName As String
    Dim p As Long
    
    folderPath = "C:\Users\user\Desktop\catalogsales\"
    
    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

However I want to convert to txt delimited columns only from each file processed the sheet called “DATASHE21”. Inside each file I have a lot of sheets but I want that one only converted to text. Now the script what does is convert to tax delimited columns the first sheet but I don’t want that. I want only converted the “DATASHE21”. What would be needed to modify in the script for that?

Thank you

>Solution :

Please, replace this code part:

    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

with this one:

    Dim wb As Workbook
    fileName = Dir(folderPath & "*.xls*")
    Do While fileName <> vbNullString
        Set wb = Workbooks.Open(folderPath & fileName)
        p = InStrRev(fileName, ".") - 1
        wb.Sheets("DATASHE21").Copy 'it creates a new workbook with a single sheet
        ActiveWorkbook.saveas fileName:=folderPath & left(fileName, p) & ".txt", FileFormat:=xlText, CreateBackup:=False
        ActiveWorkbook.Close False: wb.Close False
        fileName = Dir
    Loop