Extracting Data from Excel into .txt using VBA

[rmehta]

I have a spreadsheet with 10 worksheets in it. There are 4 worksheets named Sheet1 - Sheet4 which have 2 columns.

Resolution Eventid
a abc
b def
c efg
d hij

How would i create a VBA script to extract the content of each individual cell under Resolution add it to a text file and save the text file as the eventid cell content.

So for example the first text file to be created would contain text:

Resolution Description:

a

And the filename of the txt file shoud be abc.txt. I would like the script to go through each row and save the file in C:\temp

Any help would be much appreciated.

Regards,

Roopal

 

[Domenic]

Try...

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> CreateTextFiles()<br><br>    <SPAN style="color:#00007F">Dim</SPAN> MySheets    <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Item        <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow     <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i           <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    <SPAN style="color:#007F00">'Add/change the sheet names, accordingly</SPAN><br>    MySheets = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Item <SPAN style="color:#00007F">In</SPAN> MySheets<br>        <SPAN style="color:#00007F">With</SPAN> Worksheets(Item)<br>            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row<br>            <SPAN style="color:#00007F">If</SPAN> LastRow > 1 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> LastRow<br>                    <SPAN style="color:#00007F">Open</SPAN> "C:\Temp\" & .Cells(i, "B").Value & ".txt" <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Output</SPAN> <SPAN style="color:#00007F">As</SPAN> #1<br>                        <SPAN style="color:#00007F">Print</SPAN> #1, "Resolution Description:"<br>                        <SPAN style="color:#00007F">Print</SPAN> #1, .Cells(i, "A").Value<br>                    <SPAN style="color:#00007F">Close</SPAN> #1<br>                <SPAN style="color:#00007F">Next</SPAN> i<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> Item<br>    <br>    MsgBox "Completed...", vbInformation<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[rmehta]

Domenic,

Thanks for your help, that works perfectly.

If I wanted to save the .txt filename in uppercase how would I do that?

Thanks in advance

Regards,

Roopal

 

[Domenic]

Try replacing...

<font face=Courier New>Open "C:\Temp\" & .Cells(i, "B").Value & ".txt" For Output <SPAN style="color:#00007F">As</SPAN> #1<br></FONT>

with

<font face=Courier New><SPAN style="color:#00007F">Open</SPAN> "C:\Users\Domenic\Desktop\Test\" & UCase(.Cells(i, "B").Value) & ".TXT" <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Output</SPAN> <SPAN style="color:#00007F">As</SPAN> #1</FONT>

 

[rmehta]

Perfect domenic, thank younvery much for your help