Remove Excel Sheets

[NikoReb]

I have over 200+ excels in a Directory and am trying to remove sheets in the tabs to have one specific sheet in each. Each excel has 2-3 sheets in each. Any help would be greatly appreciated!

[Luke_C]

Hi @NikoReb 

I don't think this is something you can do out of the box in Alteryx, unless you don't care about saving the formatting on that specific sheet (which I presume you do). If you don't then you could point to a directory and use a batch macro to read each file/tab in and write it right back out. 

Alternatively, here's a VBA approach (from ChatGPT so use at your own risk 😉) 

To automate the process of removing sheets in the tabs and having one specific sheet in each of the 200+ Excel files in a directory, you can use VBA code. Here's an example of how you can achieve this:

1. Open a new Excel workbook and press Alt + F11 to open the VBA editor.
2. Insert a new module by clicking on "Insert" > "Module".
3. In the module, paste the following VBA code:

Sub RemoveSheets()
    Dim FolderPath As String
    Dim FileName As String
    Dim wb As Workbook
    Dim ws As Worksheet

    ' Set the folder path where the Excel files are located
    FolderPath = "C:\YourFolderPath\"

    ' Loop through each file in the folder
    FileName = Dir(FolderPath & "*.xlsx")
    Do While FileName <> ""
        ' Open the file
        Set wb = Workbooks.Open(FolderPath & FileName)

        ' Loop through each sheet in the workbook
        For Each ws In wb.Sheets
            ' Check if the sheet is the specific sheet you want to keep
            If ws.Name <> "Sheet1" Then
                ' Delete the sheet
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            End If
        Next ws

        ' Save and close the workbook
        wb.Close SaveChanges:=True

        ' Move to the next file
        FileName = Dir
    Loop
End Sub

4. Modify the FolderPath variable to the actual path where your Excel files are located.
5. Modify the If ws.Name <> "Sheet1" condition to match the name of the specific sheet you want to keep in each file.
6. Press F5 or click on the "Run" button to execute the code.

This VBA code will loop through each Excel file in the specified folder, delete all sheets except the specific sheet you want to keep, and save the changes. Make sure to test this code on a backup of your files before running it on your actual files.

Note: This code assumes that all the files in the folder have the .xlsx extension. If your files have a different extension, modify the Dir(FolderPath & "*.xlsx") line accordingly.

[rzdodson]

@NikoRebyou may be able to achieve this with a Run Command tool with the code from @Luke_C. The only addendum I would suggest here is adding the following lines of code after your variable declarations (the Dim statements) to ensure your macro doesn't take a full millennium to run. :)

'Variable declaration

   Dim statements

'Turn off Excel functions

With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With

[Insert rest of code] 

'Turn on Excel functions

With Application
.ScreenUpdating = True
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
End With