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