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

"Chris" wrote:

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

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

"Mike H" wrote:

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

"Chris" wrote:

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

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" wrote:

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

"Mike H" wrote:

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

"Chris" wrote:

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,

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" wrote:

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

"Mike H" wrote:

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

"Chris" wrote:

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

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" wrote:

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" wrote:

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

"Mike H" wrote:

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

"Chris" wrote:

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,

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

"Chris" wrote:

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" wrote:

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

"Mike H" wrote:

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

"Chris" wrote:

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

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

Mike

"Chris" wrote:

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" wrote:

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" wrote:

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

"Mike H" wrote:

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

"Chris" wrote:

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

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" wrote:

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

"Chris" wrote:

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" wrote:

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

"Mike H" wrote:

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

"Chris" wrote:

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,

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" wrote:

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" wrote:

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

"Chris" wrote:

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" wrote:

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

"Mike H" wrote:

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

"Chris" wrote:

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

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" wrote:

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

Mike

"Chris" wrote:

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" wrote:

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" wrote:

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

"Mike H" wrote:

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

"Chris" wrote:

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,

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


"Chris" wrote:

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" wrote:

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

Mike

"Chris" wrote:

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" wrote:

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" wrote:

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

"Mike H" wrote:

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

"Chris" wrote:

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