Splitting Rows into Multipule Files with Custom Naming.

[wearymicrobe]

I have a list of ~30,000 row's of data. The file name I want in Column B and the raw text data in A. I need to Split each individual rows into multiple Files to load them into a obscure piece of software that we use for data analysis.

Using the forum I have found this code from Dave Peterson which I have tested and used to breakup my data. Unfortunately it labels each file by increasing number. With as many data points as we have its getting difficult to say the least to cross reference by number.


DATA EXAMPLE

TGCCTGAGAGTTGCCCACGTCGGAGCCCCGGGCCAGGCT    21Pos_D_Reverse
CACTGCCTGAGAGTTGCCGTCCTCGGAGCCCCGGGCCAG    22Pos_D_Reverse
ACACACTGCCTGAGAGTTGTCCACCTCGGAGCCCCGGGC    23Pos_D_Reverse
AGGACACACTGCCTGAGAGTCGCCCACCTCGGAGCCCCG    24Pos_D_Reverse
CCCAGGACACACTGCCTGGTCGTTGCCCACCTCGGAGCC    25Pos_D_Reverse
AGTCCCAGGACACACTGCGTCAGAGTTGCCCACCTCGGA    26Pos_D_Reverse
CAGAGTCCCAGGACACACGTCCTGAGAGTTGCCCACCTC    27Pos_D_Reverse
ATTCAGAGTCCCAGGACAGTCTGCCTGAGAGTTGCCCAC    28Pos_D_Reverse
GCCATTCAGAGTCCCAGGGTCCACTGCCTGAGAGTTGCC    29Pos_D_Reverse 

WORKING CODE

Please Login or Register  to view this content.

So I have been playing with the above code to try and make a new file name based on the data in column B but to no avail. I can get it to reference A1 but not continue beyond that. not sure if excel can even use a "cell" to name a file at this point.

Please Login or Register  to view this content.

The changes are in bold. Is there some sort of naming convention command that I could use in the top code to reference the cell that I want, or am I on the right path. Any help or fixes to the code would be greatly appreciated. I am out of my element at this point.

Additionally I can put the file names on another sheet and link to that so that the file names do not end up in the data files.

[tigeravatar]

wearymicrobe,

Welcome to the forum!
Give this a try:

Please Login or Register  to view this content.

[wearymicrobe]

Well its definitely naming the files correctly, unfortunately it seems to be inserting the file name into the CSV file as well which will give my data analysis code a fit. I am going to keep working on it tonight but you have definitly put me on the right footing.


This is what I think was giving me fits, I never thought to use ".Parent.SaveAs strPath & "\" & ACell.Offset(, 1).Text & ".xls", xlExcel8"

Please Login or Register  to view this content.

[tigeravatar]

If you don't need the entire row, just delete the .EntireRow part from ACell.EntireRow.Copy