cyber-33
asked on
Excel Sublist Generation
I need a macro that can scan through an excel document, select subsets of info from this excel document and write this info to separate text files.
Specifically, the excel document looks like this:
Column A: name of the text file
Column C: text
Column D: New doc indication - can be "Y" or blank.
The script should:
1. Create a parameter that indicates the folder location where the newly created files will be written to.
2. Scan through the active spreadsheet, starting from the first row and ending with the last row.
3. create a new text file every time it encounters a "Y" value in column D of the currently scanned row. The name of the text file should be value stored in column A of the same row.
4. The context of the text file should be each line stored in Column C starting and including the row that has the "Y" value in row D, and up to and NOT including the either the next occurrence of Y value in Column D, or the end of the spreadsheet. Each line should be written separately - one below another.
For example, scanning of the spreadsheet like the one below should create 3 files A.txt, D.txt and I.txt.:
Name Text New Doc
A IMAGES\001\S-SDF-905-00000 01.jpg Y
B IMAGES\001\S-SDF-905-00000 02.jpg
C IMAGES\001\S-SDF-905-00000 03.jpg
D IMAGES\001\S-SDF-905-00000 04.jpg Y
E IMAGES\001\S-SDF-905-00000 05.jpg
F IMAGES\001\S-SDF-905-00000 06.jpg
G IMAGES\001\S-SDF-905-00000 07.jpg
H IMAGES\001\S-SDF-905-00000 08.jpg
I IMAGES\001\S-SDF-905-00000 09.jpg Y
J IMAGES\001\S-SDF-905-00000 10.jpg
K IMAGES\001\S-SDF-905-00000 11.jpg
L IMAGES\001\S-SDF-905-00000 12.jpg
A.txt should contain
IMAGES\001\S-SDF-905-00000 01.jpg
IMAGES\001\S-SDF-905-00000 02.jpg
IMAGES\001\S-SDF-905-00000 03.jpg
And so on.
Thank you for your help!
Specifically, the excel document looks like this:
Column A: name of the text file
Column C: text
Column D: New doc indication - can be "Y" or blank.
The script should:
1. Create a parameter that indicates the folder location where the newly created files will be written to.
2. Scan through the active spreadsheet, starting from the first row and ending with the last row.
3. create a new text file every time it encounters a "Y" value in column D of the currently scanned row. The name of the text file should be value stored in column A of the same row.
4. The context of the text file should be each line stored in Column C starting and including the row that has the "Y" value in row D, and up to and NOT including the either the next occurrence of Y value in Column D, or the end of the spreadsheet. Each line should be written separately - one below another.
For example, scanning of the spreadsheet like the one below should create 3 files A.txt, D.txt and I.txt.:
Name Text New Doc
A IMAGES\001\S-SDF-905-00000
B IMAGES\001\S-SDF-905-00000
C IMAGES\001\S-SDF-905-00000
D IMAGES\001\S-SDF-905-00000
E IMAGES\001\S-SDF-905-00000
F IMAGES\001\S-SDF-905-00000
G IMAGES\001\S-SDF-905-00000
H IMAGES\001\S-SDF-905-00000
I IMAGES\001\S-SDF-905-00000
J IMAGES\001\S-SDF-905-00000
K IMAGES\001\S-SDF-905-00000
L IMAGES\001\S-SDF-905-00000
A.txt should contain
IMAGES\001\S-SDF-905-00000
IMAGES\001\S-SDF-905-00000
IMAGES\001\S-SDF-905-00000
And so on.
Thank you for your help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER