The sub below is my solution to extract a range out of a
workbook to a text file. The name is hard coded into the
sub below, but I'd rather have a way to have the sub pick
up the file name to save to from a spreadsheet range that
I have.

I tried things like:

Dim extractfile as string
Extractfile = Range("Extract_file").value

So, I don't even know if string is the right thing to use
in this case. But, the value in the range is calculated as
a different name depending on another range in the
spreadsheet. The ranges value in this case
is "D:\MyFiles\DATA\TTAX\200306rept.Txt"


So, the line below should work like this:
filename:="Extractfile"&", FileFormat:=xlText"




Sub Extract_File()
Application.DisplayAlerts = False

With Worksheets("report").Range("A1")
.CurrentRegion.Copy
End With
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
filename:="D:\MyFiles\DATA\Ttax\ExtractedRpt.txt", _
FileFormat:=xlText
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

Bruce,

You didn't exactly pinpoint your problem?

But if it isn't working may I suggest removing the quotes
from around your variable name. ie.
ActiveWorkbook.SaveAs filename:=Extractfile, FileFormat:=xlText

Dan E

"Bruce Roberson" wrote in message ...
The sub below is my solution to extract a range out of a
workbook to a text file. The name is hard coded into the
sub below, but I'd rather have a way to have the sub pick
up the file name to save to from a spreadsheet range that
I have.

I tried things like:

Dim extractfile as string
Extractfile = Range("Extract_file").value

So, I don't even know if string is the right thing to use
in this case. But, the value in the range is calculated as
a different name depending on another range in the
spreadsheet. The ranges value in this case
is "D:\MyFiles\DATA\TTAX\200306rept.Txt"


So, the line below should work like this:
filename:="Extractfile"&", FileFormat:=xlText"




Sub Extract_File()
Application.DisplayAlerts = False

With Worksheets("report").Range("A1")
.CurrentRegion.Copy
End With
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
filename:="D:\MyFiles\DATA\Ttax\ExtractedRpt.txt", _
FileFormat:=xlText
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

Hi Bruce,

It should be just:

ActiveWorkbook.SaveAs filename:=Extractfile, FileFormat:=xlText

The string variable is a direct replacement for the hard-coded string.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Bruce Roberson" wrote in message
...
The sub below is my solution to extract a range out of a
workbook to a text file. The name is hard coded into the
sub below, but I'd rather have a way to have the sub pick
up the file name to save to from a spreadsheet range that
I have.

I tried things like:

Dim extractfile as string
Extractfile = Range("Extract_file").value

So, I don't even know if string is the right thing to use
in this case. But, the value in the range is calculated as
a different name depending on another range in the
spreadsheet. The ranges value in this case
is "D:\MyFiles\DATA\TTAX\200306rept.Txt"


So, the line below should work like this:
filename:="Extractfile"&", FileFormat:=xlText"




Sub Extract_File()
Application.DisplayAlerts = False

With Worksheets("report").Range("A1")
.CurrentRegion.Copy
End With
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
filename:="D:\MyFiles\DATA\Ttax\ExtractedRpt.txt", _
FileFormat:=xlText
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

Have you set a watch on filename, your example

filename:="Extractfile"&", FileFormat:=xlText"
with Extractfile in quotes filename would be

"ExtractfileFileFormat:=xlText"

filename:=Extractfile&" "&", FileFormat:=xlText"

Taking off the quotes and adding a space would be

"D:\MyFiles\DATA\TTAX\200306rept.Txt FileFormat:=xlText"

Lance
-----Original Message-----
The sub below is my solution to extract a range out of a
workbook to a text file. The name is hard coded into the
sub below, but I'd rather have a way to have the sub pick
up the file name to save to from a spreadsheet range that
I have.

I tried things like:

Dim extractfile as string
Extractfile = Range("Extract_file").value

So, I don't even know if string is the right thing to use
in this case. But, the value in the range is calculated
as
a different name depending on another range in the
spreadsheet. The ranges value in this case
is "D:\MyFiles\DATA\TTAX\200306rept.Txt"


So, the line below should work like this:
filename:="Extractfile"&", FileFormat:=xlText"




Sub Extract_File()
Application.DisplayAlerts = False

With Worksheets("report").Range("A1")
.CurrentRegion.Copy
End With
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs
filename:="D:\MyFiles\DATA\Ttax\ExtractedRpt.txt" , _
FileFormat:=xlText
ActiveWindow.Close
Application.DisplayAlerts = True
End Sub

.