As the workbook containing the range name is probably not active when you execute your code you probably need to be more specific
about which workbook you mean.

Extractfile = Workbooks("Book1.xls").Worksheets("Sheet1").Range( "Extract_file").Value
--

John Green - Excel MVP
Sydney
Australia


"Bruce Roberson" wrote in message ...
John:


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

when you have "extractfile", you're saying the variable I declared as a
string, right, not the spreadsheet cell Extract_file where the result of
that cell is D:\MyFiles\DATA\TTAX\200306rept.Txt ?

The problem then with that is that when I did the
Dim extractfile as string
Extractfile = Range("Extract_file").value

Excel VBA did not take those command line, line 2 in this case bombed, with
some run time error.




--

John Green - Excel MVP
Sydney
Australia


"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