Loop all excel files in a directory and convert each worksheet of the excel files to text

Hello all,

This is my first time with VBA Macros in excel. My query may be a basic one but appreciate any help on this.

I have a folder where I have multiple excel( .xlsx) files with 2 or more worksheets.

e.g

00.XLSX
01.XLSX
02.XLSX

Each of these has 2 worksheets a)Header b) Detail

My requirement is that the macro should loop over each of the excel files in the folder and select each of the worksheets convert it into a text file. So basically my output would look like ( files converted)

00_Header.txt
00_Detail.txt
01_Header.txt
01_detail.txt
02_Header.txt
02_Detail.txt


I found various snippets here and I framed a bit of code of my own. But Now I am stuck up here especially in the loop for handling the worksheets of the workbook.
Any help would be really appreciated and I would be thankful for it.

Code:
===

Please Login or Register  to view this content.

Please Login or Register  to view this content.

=====

Thanks.

Moderator's Edit: Use code tags when posting code. To do so in future, select the code and click on the # icon at the top of your post window.

Add a trailing backslash ("\") to strpath.

You can replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and drop your 'GetBookName' function.

Also, append the desired enumeration of xlFileFormat (eg. xlCSV) to the line

Please Login or Register  to view this content.

so that you get

Please Login or Register  to view this content.

which will save in Comma Separated Value format, which is probably what you want for an Excel worksheet.

Having looked at this again, the solution is actually simpler than I first thought.

However, before I go into that I wanted to point out the problem with your code: xlText is not a valid xlFileFormat enumeration. You should use either xlTextWindows or xlTextPrinter. xlTextWindows will save the text file with each column delimited by a single tab. xlTextPrinter will save the text file with columns delimited by multiple spaces. Experiment with both to see what you want.

I did some Google-ing and found that the Worksheet object has its own SaveAs method, ie. there's no need to mess about creating another workbook, copying a worksheet to it and saving the workbook as a text file. Instead, you can simply loop through the worksheets in a workbook and choose to save (or not) each one as a separate file. For example:

Please Login or Register  to view this content.

Some points to note:
1. I've used the Replace function and hard-coded the string to find, ie. ".xlsx", and replaced it with an empty string (""). It's more flexible if you keep your original 'objFso.GetExtensionName' code and use a variable for the find string.
2. 'Application.DisplayAlerts = False' stops the annoying prompt that asks you if you want to save the file. I switch it back on after everything's been saved.
3. Unfortunately, a side effect of Worksheet.SaveAs is that your workbook gets renamed every time - BE AWARE OF THIS. You may want to put the original filename in a variable and at the very end of the macro, re-save the workbook with this name. Alternatively, you could close the workbook without saving it - but you'll lose any changes (incl. VBA code) if you haven't saved them already so best save it first before you run the macro.

I hope this helps.