Help saving a select to a text file

  • Thread starter msnews.microsoft.com
  • 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.
 
T

Tom Ogilvy

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
 
M

msnews.microsoft.com

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?
 
T

Tom Ogilvy

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.
 
T

Tom Ogilvy

Just to be sure, change
Workbooks.Add

to

Workbooks.Add Template:=xlWBATWorksheet

to make sure you create a single sheet workbook.
 
Top