Help saving a select to a text file

  • Thread starter Thread starter msnews.microsoft.com
  • Start date Start date
M

msnews.microsoft.com

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
 
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?
 
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.
 
Just to be sure, change
Workbooks.Add

to

Workbooks.Add Template:=xlWBATWorksheet

to make sure you create a single sheet workbook.
 
Back
Top