Export, Name, Save as Text File

[golfnut324]

Could someone tell me the best way to tackle this issue? I have a three column table (A, B & C) that looks like this:

Book1.xls
	A	B	C	D
1	ITEM	TEXT	FILEEXT
2	AAC42011	VERYLONGTEXTHERE	.txt
3	AVV55432	VERYLONGTEXTHERE	.txt
4	XXR66575	VERYLONGTEXTHERE	.txt
5	>1100rows
Sheet1

Table has about 1100 rows with item number, long text and .txt as above. I would like to export each text field to a Notepad (or other text) file and save the file as ""A".txt".

Is this best handled via VBA from Excel or from Word or...? Being that I barely know how to spell V B A, is this high level and should I look for professional help?

Thanks for any help and/or advice on this question.

Craig

 

[GettingThere]

Hi Golfnut324,

Can I check I unserstand correctly? For each row you want a seperate text file, named after the item number and the text being the text from column b adjacent (so this will create c. 1100 files...)

If so, try the following:

Sub Export_To_TextFile()
Range("a2").Select
Do While Not ActiveCell = ""
    Set fso = CreateObject("Scripting.Filesystemobject")
    Set ts = fso.CreateTextFile(ActiveCell.Value & ".txt", True)
 
    ts.Write ActiveCell.Offset(, 1).Text
 
    Set fso = Nothing
    Set ts = Nothing
 
    ActiveCell.Offset(1).Select
Loop
End Sub

I've tested this and it appears to work fine - It will save the files to your default (normally My Documents) - so you need to be sure that there aren't currently text files with conflicting file names that you need to keep because this file will write over them...

Let me know if there's anything else you need (are you ok with inserting modules etc to input the code?).

Hope that helps.


Arthur =D

 

[golfnut324]

Thank you so much for the help. I will try it right now and post back. I can enter code into module, sometimes I get confused on how to call the code but I will try.

Thanks again!

 

[golfnut324]

Hi Golfnut324,

Can I check I unserstand correctly? For each row you want a seperate text file, named after the item number and the text being the text from column b adjacent (so this will create c. 1100 files...)

If so, try the following:

Sub Export_To_TextFile()
Range("a2").Select
Do While Not ActiveCell = ""
    Set fso = CreateObject("Scripting.Filesystemobject")
    Set ts = fso.CreateTextFile(ActiveCell.Value & ".txt", True)
 
    ts.Write ActiveCell.Offset(, 1).Text
 
    Set fso = Nothing
    Set ts = Nothing
 
    ActiveCell.Offset(1).Select
Loop
End Sub

I've tested this and it appears to work fine - It will save the files to your default (normally My Documents) - so you need to be sure that there aren't currently text files with conflicting file names that you need to keep because this file will write over them...

Let me know if there's anything else you need (are you ok with inserting modules etc to input the code?).

Hope that helps.


Arthur =D

ABSOLUTELY OUTSTANDING!! Thank you, Thank you and Thank you!

Craig

 

[GettingThere]

Any time lol

 

[Krishnakumar]

removed