Pass file name from Spreadsheet Range

[Bruce Roberson]

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

 

[Dan E]

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

 

[Rob Bovey]

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 *

 

[Lance]

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

 

[Bruce Roberson]

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]

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