Code to Export Excel Data to Text File

I need some script to export excel data to a text file. I have found a lot of these online, but my situation is a little tricky. I have 100s of excel sheets with a variable number of groups. These goups consist of 5 columns full of data and between each group there is a blank column. For example, I could have 2 groups as shown below (letters are to show columns).

A B C D E F G H I J K
1 4 7 1 4 1 2 3 4 5
2 5 8 2 5 6 7 8 9 1
3 6 9 3 6 2 3 4 5 6

Here column F is blank and I want the data exported to a text file so each group goes vertically under the previous group. So the above excel data would be exported to a text file as follows:
1 4 7 1 4
2 5 8 2 5
3 6 9 3 6
1 2 3 4 5
6 7 8 9 1
2 3 4 5 6

Each of my excel worksheets has a variable number of groups and each group does contain a blank column between it. I need a script that would take an excel file with a variable number of these groups and export the data to a text file like I described above. I am new to this and not very farmiliar with it and therefore do not really know how to go about doing this. I am trying to save a lot of time rather than manually going into each worksheet and copying the data to a text file following the above guidelines.

Any help is appreciated. thanks.

Hello Ashkash,

Are all your data numbers?

Sincerely,
Leith Ross

Mostly numbers, but there are cells that also contain characters.

Hello Ashkash,

Are all the worksheets in the same workbook? Are you using Office 2007?

Sincerely,
Leith Ross

Yes, all the worksheets are in the same workbook. Using Excel 2003.

Hello Ashkash,

I am working on the code now, and almost have it working. It shouldn't take me much longer to finish it.

Sincerely,
Leith Ross

Hello Ashkash,

Here is the macro. You will need to change the text file name (marked in blue) to what you want. Place this in a standard VBA Module.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel

Sincerely,
Leith Ross

Just tried the code and I get the following error when running the macro:
Runtime error '1004':
Method 'Range of object' _Worksheet' failed

Attached is the excel file I am testing it with. Any ideas? thanks.

I am still getting the same error as above, but it does create the text file but it is not formatted to the specifications. Could this be because it is not going through the code correctly because of the error? If you look at the attached text excel file in my previous post, I need the data in columns A-E exported to the text file and then the text file should go to the next line and export columns G-K and then it should go to the next line and export columns M-Q and so on. The test excel file only has 3 five column groups, but the number of these 5 column groups in my data is variable. So each group of 5 columns is exported to the text file and being vertically stacked in format. The code might be doing this, but maybe because of the error it is not executing as it should. Attached is an example of how the created text file should look for the test excel data.

Hello Ashkash,

This turned about to be more difficult than I expected. Finally have it working. You can run the macro using CTRL+SHIFT+S. This will automatically save every worksheet in the workbook using the custom format. Here is the macro code. It is already installed in the attached workbook.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Thanks a lot. Works prefectly. This will definetly save me a lot of time. I really appreciate all your help. thanks again.

Please Login or Register  to view this content.

I think one article is very useful to export data to Text file. It introduces us a simple way to complete the exportation quickly. If you are interested in it, you can view it on
http://www.codeproject.com/KB/cs/Exc...ExportWiz.aspx

Hello
I have a work book with 60 sheets each sheet has alot of rows
I would like to export each row as a text file, nameing the text file from column A placing everything in the row into the text file.
in total it will be over 32000 text files way to many to do manualy

can anyone offer some generious help it would be greatly appriciated.
thanks