Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I pass arguments to my formatted empty spreadsheet file? | Excel Discussion (Misc queries) | |||
Can I pass network logon name to a cell in a spreadsheet? | Excel Worksheet Functions | |||
How to calculate pass/fail percentages entered on a spreadsheet? | New Users to Excel | |||
How do I pass series data x-value range to a chart from a cell? | Charts and Charting in Excel | |||
How to (re)set a range.value to pass -0- to a "double" variable | Excel Discussion (Misc queries) |