Pass file name from Spreadsheet Range
 

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

Pass file name from Spreadsheet Range
 

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

Pass file name from Spreadsheet Range
 

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

Pass file name from Spreadsheet Range
 

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

.

Pass file name from Spreadsheet Range
 

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

Pass file name from Spreadsheet Range
 

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