Marco To Save Column A7 As A .txt File.

  • Thread starter Thread starter Chris
  • Start date Start date
C

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
 
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:D" & 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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top