How do I programmatically extract data from a worksheet (a range, not the
entire sheet) to a text file?

What I am doing now is selecting the data I want with my mouse and copying
it. I then paste it to notepad and save it. I would like to do this
automatically from an external script, preferably VBScript.


Thanks in advance.

In code: create a new workbook, copy the range to the new workbook, saveas a
text file.

Dim rng as Range
set rng = Range("A1").CurrentRegion
Workbooks.Add
rng.copy Destination:=Activesheet.Range("A1")
Activeworkbook.SaveAs Filename:="C:\data\mytext.csv, _
Fileformat:=xlCSV
Activeworkbook.close Savechanges:=False

--
Regards,
Tom Ogilvy


msnews.microsoft.com wrote in message
...
How do I programmatically extract data from a worksheet (a range, not the
entire sheet) to a text file?

What I am doing now is selecting the data I want with my mouse and copying
it. I then paste it to notepad and save it. I would like to do this
automatically from an external script, preferably VBScript.


Thanks in advance.

Thanks Tom.

Using your code, I am able to create the file, but there is nothing in it.

I have data in A1, but it doesn't get copied to the new worksheet.

Any ideas?




"Tom Ogilvy" wrote in message
...
In code: create a new workbook, copy the range to the new workbook, saveas
a
text file.

Dim rng as Range
set rng = Range("A1").CurrentRegion
Workbooks.Add
rng.copy Destination:=Activesheet.Range("A1")
Activeworkbook.SaveAs Filename:="C:\data\mytext.csv, _
Fileformat:=xlCSV
Activeworkbook.close Savechanges:=False

--
Regards,
Tom Ogilvy


msnews.microsoft.com wrote in message
...
How do I programmatically extract data from a worksheet (a range, not
the
entire sheet) to a text file?

What I am doing now is selecting the data I want with my mouse and
copying
it. I then paste it to notepad and save it. I would like to do this
automatically from an external script, preferably VBScript.


Thanks in advance.

As written, the code assumes, that when you run it, the worksheet with the
data is the activesheet and the data starts in Cell A1.
it creates a reference (rng) to the data starting in the upperleft corner
of the activesheet
it creates a new workbook
if copies the original data to cell A1 of the activesheet in the new
workbook
it saves the new workbook as a csv file
it closes the new workbook

Beyond that, it should work.
--
Regards,

"msnews.microsoft.com" wrote in message
...
Thanks Tom.

Using your code, I am able to create the file, but there is nothing in it.

I have data in A1, but it doesn't get copied to the new worksheet.

Any ideas?




"Tom Ogilvy" wrote in message
...
In code: create a new workbook, copy the range to the new workbook,
saveas
a
text file.

Dim rng as Range
set rng = Range("A1").CurrentRegion
Workbooks.Add
rng.copy Destination:=Activesheet.Range("A1")
Activeworkbook.SaveAs Filename:="C:\data\mytext.csv, _
Fileformat:=xlCSV
Activeworkbook.close Savechanges:=False

--
Regards,
Tom Ogilvy


msnews.microsoft.com wrote in message
...
How do I programmatically extract data from a worksheet (a range, not
the
entire sheet) to a text file?

What I am doing now is selecting the data I want with my mouse and
copying
it. I then paste it to notepad and save it. I would like to do this
automatically from an external script, preferably VBScript.


Thanks in advance.

Just to be sure, change
Workbooks.Add

to

Workbooks.Add Template:=xlWBATWorksheet

to make sure you create a single sheet workbook.

--
Regards,
Tom Ogilvy

"msnews.microsoft.com" wrote in message
...
Thanks Tom.

Using your code, I am able to create the file, but there is nothing in it.

I have data in A1, but it doesn't get copied to the new worksheet.

Any ideas?




"Tom Ogilvy" wrote in message
...
In code: create a new workbook, copy the range to the new workbook,
saveas
a
text file.

Dim rng as Range
set rng = Range("A1").CurrentRegion
Workbooks.Add
rng.copy Destination:=Activesheet.Range("A1")
Activeworkbook.SaveAs Filename:="C:\data\mytext.csv, _
Fileformat:=xlCSV
Activeworkbook.close Savechanges:=False

--
Regards,
Tom Ogilvy


msnews.microsoft.com wrote in message
...
How do I programmatically extract data from a worksheet (a range, not
the
entire sheet) to a text file?

What I am doing now is selecting the data I want with my mouse and
copying
it. I then paste it to notepad and save it. I would like to do this
automatically from an external script, preferably VBScript.


Thanks in advance.