Remove Excel Sheets
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Labels:
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
- Open a new Excel workbook and press Alt + F11 to open the VBA editor.
- Insert a new module by clicking on "Insert" > "Module".
- 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
- Modify the
FolderPath
variable to the actual path where your Excel files are located. - Modify the
If ws.Name <> "Sheet1"
condition to match the name of the specific sheet you want to keep in each file. - 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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
