How to Export a Specific Named Worksheet to TXT Using Excel VBA
Question details
The user needs to export a specific worksheet (e.g., "DATASHE21") from multiple Excel workbooks to individual TXT files using VBA.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the extraction of a specific data sheet from multiple Excel workbooks into separate text files.
- Observed behavior
- Modifying the Dir file loop condition fails to isolate the worksheet, as Dir only filters external file names, not the internal worksheet tabs.
Ensure all the source Excel workbooks you want to process are placed in a single dedicated folder, and verify the exact spelling and capitalization of the target worksheet name (e.g., 'DATASHE21').
Use VBA to Loop Through Workbooks and Match Worksheet Names
Open each file in your target directory, loop through its internal worksheets, and execute the export only if the sheet name matches your target.
Using the `Dir` function only interacts with the file system to filter file names. To export a specific worksheet, your macro must open each workbook, iterate through its sheets, and check the `sh.Name` property before executing the 'SaveAs' command.
Press Alt + F11 in Excel to open the Visual Basic for Applications Editor. Go to Insert > Module to create a new blank script area.
At the beginning of your script, add `Application.DisplayAlerts = False` and `Application.ScreenUpdating = False`. This prevents Excel from freezing the macro with prompt dialogues when saving as a TXT file format.
Use the `Dir` function to loop through all `.xlsx` files in your folder. Inside the file loop, write `Set wb = Workbooks.Open(fileName)` to open the file, then nest a loop using `For Each sh In wb.Worksheets`.
Inside the worksheet loop, add the condition `If sh.Name = "DATASHE21" Then`. If matched, copy the sheet to a new workbook (`sh.Copy`), and use `ActiveWorkbook.SaveAs Filename:=uniqueName, FileFormat:=xlText`.
Assign a unique output filename (e.g., by combining the original workbook name with the sheet name) for the `uniqueName` variable to prevent the macro from overwriting previously exported files.
Automate Data Extraction Seamlessly in WPS Spreadsheets
WPS Office provides robust support for VBA macros, allowing you to run your exact custom scripts to extract and export specific worksheets directly to TXT formats.
- 1. Enable Developer Tools: Open WPS Spreadsheets, navigate to the Developer tab on the top ribbon, and ensure Macros are enabled for your workspace.
- 2. Access the Visual Basic Editor: Click the 'Visual Basic' button or press Alt + F11 to launch the macro editor integrated directly into WPS Office.
- 3. Paste and Run Your Code: Insert a new module, paste your VBA worksheet-exporting script, and click the 'Run' icon or press F5 to process your files.
Frequently Asked Questions
Why does changing the Dir loop condition fail to find my worksheet?
The `Dir` function in VBA only interacts with the Windows file system to find physical file names (like .xlsx files). It cannot read or filter the internal contents of those files, such as worksheet tab names. You must open the workbook first to read sheet names.
How do I prevent the macro from overwriting my exported TXT files?
When using the `SaveAs` method, dynamically append the source workbook's name or a unique counter to the output file name string. For example, use `Filename:="C:\OutputFolder\" & sh.Name & "_" & wb.Name & ".txt"`.
Can I export multiple specific sheets instead of just one?
Yes. You can modify the `If` statement to evaluate multiple conditions using `Or` (e.g., `If sh.Name = "DATASHE21" Or sh.Name = "DATASHE22" Then`), or use a `Select Case sh.Name` structure for cleaner code when targeting many specific sheets.
Why am I getting privacy or compatibility warning prompts when the macro runs?
Excel automatically triggers compatibility warnings when you save worksheets in non-standard formats like plain text (TXT). You can seamlessly bypass these prompts by inserting `Application.DisplayAlerts = False` immediately before your `SaveAs` command.
