Modify the code to save files in xlsx format

sofas

Well-known Member
Joined
Sep 11, 2022
Messages
559
Office Version
  1. 2021
  2. 2019
Platform
  1. Windows
Hello, I want to modify the code so that files are saved in xlsx format in the same directory path


VBA Code:
Sub Worksheets_to_txt()                               '<--Saves each worksheet as a text file with the same name
    
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FileSavePathName As String
    Dim answer As VbMsgBoxResult
    Dim DoExport As Boolean
    
    Set WB = ActiveWorkbook
    
    answer = MsgBox("Are you sure you want to export worksheets?", vbYesNo, "Run Macro") '<--Pop up box to confirm export
    
    If answer = vbYes Then
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        For Each WS In WB.Worksheets
            Select Case WS.Name
                Case "Sheet1", "Sheet2", "Sheet4"         'list worksheets to export here
                    FileSavePathName = WB.Path & "\" & WS.Name & "_" & VBA.Format(Date, "YYYYMMDD") & ".txt"
                    DoExport = True
                    
                    'Msgbox code can be removed later if desired
                    Select Case MsgBox("Export worksheet?" & vbCr & vbCr & FileSavePathName, vbYesNoCancel Or vbQuestion, "Text File Export")
                        Case vbYes
                            DoExport = True
                        Case vbNo
                            DoExport = False
                        Case vbCancel
                            Exit Sub
                    End Select
                    
                    If DoExport Then
                        WS.Copy                           'make temporary workbook
                        With ActiveWorkbook
                            .SaveAs Filename:=FileSavePathName, FileFormat:=xlText, CreateBackup:=False 'export as textfile.
                            DoEvents
                            .Close False                  'close temporary workbook
                        End With
                    End If
            End Select
        Next WS
    End If
End Sub
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hello, I want to modify the code so that files are saved in xlsx format in the same directory path
Right now your code saves to a .txt file. Do you mean you want to change that so that it save to .xlsx instead?
 
Upvote 0
Perhaps something like this. Not tested.
VBA Code:
Sub Worksheets_to_xlxs()                               '<--Saves each worksheet as a xlsx file with the same name
    
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FileSavePathName As String
    Dim answer As VbMsgBoxResult
    Dim DoExport As Boolean
    
    Set WB = ActiveWorkbook
    
    answer = MsgBox("Are you sure you want to export worksheets?", vbYesNo, "Run Macro") '<--Pop up box to confirm export
    
    If answer = vbYes Then
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        For Each WS In WB.Worksheets
            Select Case WS.Name
                Case "Sheet1", "Sheet2", "Sheet4"         'list worksheets to export here
                    FileSavePathName = WB.Path & "\" & WS.Name & "_" & VBA.Format(Date, "YYYYMMDD") & ".xlsx"  '".txt"
                    DoExport = True
                    
                    'Msgbox code can be removed later if desired
                    Select Case MsgBox("Export worksheet?" & vbCr & vbCr & FileSavePathName, vbYesNoCancel Or vbQuestion, "Worksheet Export") '"Text File Export")
                        Case vbYes
                            DoExport = True
                        Case vbNo
                            DoExport = False
                        Case vbCancel
                            Exit Sub
                    End Select
                    
                    If DoExport Then
                        WS.Copy                           'make temporary workbook
                        With ActiveWorkbook
                            '.SaveAs Filename:=FileSavePathName, FileFormat:=xlText, CreateBackup:=False 'export as textfile.
                            .SaveAs Filename:=FileSavePathName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False 'export as .xlsx
                            DoEvents
                            .Close False                  'close temporary workbook
                        End With
                    End If
            End Select
        Next WS
    End If
End Sub
 
Upvote 0
Solution
Perhaps something like this. Not tested.
VBA Code:
Sub Worksheets_to_xlxs()                               '<--Saves each worksheet as a xlsx file with the same name
   
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim FileSavePathName As String
    Dim answer As VbMsgBoxResult
    Dim DoExport As Boolean
   
    Set WB = ActiveWorkbook
   
    answer = MsgBox("Are you sure you want to export worksheets?", vbYesNo, "Run Macro") '<--Pop up box to confirm export
   
    If answer = vbYes Then
       
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
       
        For Each WS In WB.Worksheets
            Select Case WS.Name
                Case "Sheet1", "Sheet2", "Sheet4"         'list worksheets to export here
                    FileSavePathName = WB.Path & "\" & WS.Name & "_" & VBA.Format(Date, "YYYYMMDD") & ".xlsx"  '".txt"
                    DoExport = True
                   
                    'Msgbox code can be removed later if desired
                    Select Case MsgBox("Export worksheet?" & vbCr & vbCr & FileSavePathName, vbYesNoCancel Or vbQuestion, "Worksheet Export") '"Text File Export")
                        Case vbYes
                            DoExport = True
                        Case vbNo
                            DoExport = False
                        Case vbCancel
                            Exit Sub
                    End Select
                   
                    If DoExport Then
                        WS.Copy                           'make temporary workbook
                        With ActiveWorkbook
                            '.SaveAs Filename:=FileSavePathName, FileFormat:=xlText, CreateBackup:=False 'export as textfile.
                            .SaveAs Filename:=FileSavePathName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False 'export as .xlsx
                            DoEvents
                            .Close False                  'close temporary workbook
                        End With
                    End If
            End Select
        Next WS
    End If
End Sub
Thank you. he is working well
 
Upvote 0

Forum statistics

Threads
1,224,462
Messages
6,178,815
Members
452,881
Latest member
motivationgyan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top