Automated formatting and saving multiple files

[kerrydel]

I regularly receive files (3D point coordinates) that I must copy and save as text files for input to another program (Pro/Engineer).

Currently, I copy and paste rows/columns into WordPad and then save them as text files

The files that I get are all of the same format. The file has multiple blocks of numbers that each need to be put into a separate text file.

The original file is formatted as follows

9 rows x 3 columns
35 rows x 3 columns
35 rows x 3 columns

9 rows x 3 columns
35 rows x 3 columns
35 rows x 3 columns
.
.
.
.

...for a total of 9 'blocks'. There will be text and numbers between each group of blocks, but I can cut these out manually if it helps any, and the cells that I want don't start at A1 either.

So what this means is that I copy, paste and save 27 times to get what I want.

Is there any way of 'automating' (for lack of a better term) at least some of this work? Is it possible to do this without even using WordPad?

I am NO macro expert whatsoever, so that's why I'm looking for help.

Thanks in advance for any assistance.

Kerry DelVecchio
Peoria, IL

[Alf]

'automating' (for lack of a better term) at least some of this work

Probably but you must give a bit more information if you expects to get some help.

Upload a sample file showing what data you whish to save to a text file for a start.

You talk about copy, save and past 27 times. Do you have 3 files each with 9 blocks of data then?

Alf

[kerrydel]

I've attached an example of the file that I receive. I've highlighted the different sections that I want to turn into text files. The different colors are just to show the repeating nature of the data.

I want to create a total of 27 individual UNFORMATTED text files (one per highlighted area). I've attached one of those from the first group of data as well.

Thanks
Kerry DelVecchio

[Alf]

Hi Kerry

Perhaps this macro could be of help?

To test it run macro "Data_toText".

The macro first makes a copy of sheet1 naming it "Transfer". It then goes through all data in the "Transfer" sheet deleting each block of data as soon as a text file is created. At the end the sheet "Transfer" is deleted.

The data blocks are named "Mainblade_block_1.txt", "Mainblade_block_2.txt" and so forth.

At the moment the macro puts all the text files in the folder "C:\Test\" a thing you probably have to change.

So change the line:

Filename:="C:\Test\" & stTextName & "_block_" & j, _
    FileFormat:=xlText

to wherever you whish to collect your files. Since the macro doesn’t create folders the output of text files must be to an existent folder.

Alf

[kerrydel]

Excellent! Thank you!

Now I just need to be smart enough to figure out how it works. I'll need to modify the macro for times when I get different inputs. They are all very similar, just not EXACTLY the same as this.

I appreciate the help.

Kerry DelVecchio
Peoria, IL

[Alf]

Glad to be of help.

If this takes care of your problem don't forget to mark your thread "Solved" and if you like to rate my answer / help you can click on the small star left bottom part of my post.

To help you modify this macro I've written a word document trying to explain what this macro does. Don't hesitate to ask for help if you don't understand.

To write a macro like this you must first find a repeating pattern. In your file Mainblade all blocks of data started with a 1 in the B column so I used that. I could perhaps have tried to find "fb_" but in some cases it was "fb_" then two blank rows and then "fb_" again so I decided no to.

If all your data blocks start with a 1 but in another column the you only need to change the column part.

Alf

[kerrydel]

One other minor thing. Is there a way to 'format' the output names. This is a VERY MINOR thing but it would be nice if the output files were in order. The program I'm reading them in seems them as filename1, filename10, finename11,.... filename19, filename2, filename20.... etc. So if I could have the names start at 10 instead, or start with 01, 02, 03, etc that would be helpful

Again, this is minor, but I might as well ask.

Thanks
Kerry

[Alf]

If you cange the line

Filename:="C:\Test\" & stTextName & "_block_" & j, _
    FileFormat:=xlText

to

Filename:="C:\Test\" & stTextName & j, FileFormat:=xlText

The files will be named "Mainblade1.txt", "Mainblade2.txt", "Mainblade3.txt" and so forth.

I would recomend you read the word document I added in the previous post this explains a bit in more detail what and how thia macro does things.

If you want to name them from 10 to 37 you just add

j = 9

before the "Do Until" statment and the naming format must be as before
i.e.

Filename:="C:\Test\" & stTextName & j, FileFormat:=xlText

Alf