Marco To Save Column A7 As A .txt File.

[Chris]

Hey All,

Is it possible to set up a macro where only one column can be saved as a
text file?

i.e.

Column A,B,C,D is full of data, I would like to save Column D's information
into a txt file by a click of a button onto my desktop.

Any help would be great!

Regards,
Chris

 

[Mike H]

Chris,

Right click your sheet tab, view code and paste this in. To create a button
open the 'Forms' toolbar click on the button and create one on the desktop.
In the popup window navigate to the macro.

Sub savetext()
Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set myrange = Range("D1" & Lastrow)
filenum = FreeFile
Open "C:\myfile.txt" For Append As filenum
For Each c In myrange
Print #filenum, c.Text
Next
Close #filenum
End Sub

Mike

 

[Chris]

Hi Mike,

thanks for your reply.
this script works perfectly, apart from one littile problem.
when I click the button the txt file is created but at the top it says :
Script

is there anyway of deleting this?

Regards,
Chris

 

[Chris]

ahh nevermind sortted it lol,
It was printing the top cell which is called Script. I changed it so that it
starts from cell 2 instead of the 1st cell.

ok one last thing tho.. how can I make it so that everytime i Click the
button it saves over the top of the last file that was created?


Regards,
Chris

 

[Mike H]

Chris,

I don't understand why that is happening.
Do you mean the word "Script" appears in the textfile?
Do you have the word "Script" as a header in column D?

Mike

 

[Chris]

Yes, the top cell has the word "Script" in it lol
all changed, and its working great..

Just want to know if / how i can make it so that each time the button is
pressed it overwrites the file each time.

Regards,
Chris

 

[Mike H]

Chris,

use this line to overwrite. Note the file access mode is changed from APPEND
to OUTPUT.

Open "C:\documents and settings\inchugmi\desktop\ " & response & ".txt" For
Output As filenum

Mike

 

[Mike H]

Im sure you've spotted it but just in case to eliminate the header change D1
to D2

Mike

 

[Chris]

Hi Mike,

Ive changed it to OUTPUT, but now when I click the button the file doesnt
create.
Imnot sure what you mean by:

Open "C:\documents and settings\inchugmi\desktop\ " & response & ".txt" For
Output As filenum


my code looks like this at the moment:


Sub savetext()
Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set myrange = Range("I2:I" & Lastrow)
filenum = FreeFile
Open "C:\NT_Account\myfile.txt" For Output As filenum
For Each c In myrange
Print #filenum, c.Text
Next
Close #filenum
End Sub




Regards,
Chris

 

[Mike H]

Chris,

Apologies, I posted the wrong line I was anticipating a question on how you
could get the file on your desktop and that line was the path to my desktop
so unsurprisingly it doesn't work on yours . Use this line and the file
will be created in the root of C and will be overwritten each time.

Open "C:\myfile.txt" For Output As filenum

Mike

 

[Chris]

Hi Mike,

Ive changed it to OUTPUT, but now when I click the button the file doesnt
create.
Imnot sure what you mean by:

Open "C:\documents and settings\inchugmi\desktop\ " & response & ".txt" For
Output As filenum


my code looks like this at the moment:


Sub savetext()
Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
Set myrange = Range("I2:I" & Lastrow)
filenum = FreeFile
Open "C:\NT_Account\myfile.txt" For Output As filenum
For Each c In myrange
Print #filenum, c.Text
Next
Close #filenum
End Sub




Regards,
Chris

 

[Mike H]

Chris,

This thread is jumping about a bit and were getting lost.

Apologies, I posted the wrong line I was anticipating a question on how you
could get the file on your desktop and that line was the path to my desktop
so unsurprisingly it doesn't work on yours . Use this line and the file
will be created in the root of C and will be overwritten each time.

Open "C:\myfile.txt" For Output As filenum

You can then change the path to what you want but if you enter an incorrect
path it won't create.

Mike