Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving a file using a cell for file name
I have written the following which opens a txt file based on a location specified and then converts the format and then saves the file based on a cell address but the file is being saved using acell address on the active worksheet rather than the original one. Sub Convert() Workbooks.OpenText Filename:=ActiveSheet.Range("B15"), Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _ ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _ (20, 1)), TrailingMinusNumbers:=True Cells.Select Cells.EntireColumn.AutoFit ActiveWorkbook.SaveAs Filename:=ActiveSheet.Range("C15").Value, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.Close End Sub Can anyone help please -- Adrian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving a file using a cell for file name
Sub Convert() Dim strFileName As String strFileName = "c:\" & ActiveSheet.Range("C15").Value 'Check whether the path is mentioned... Workbooks.OpenText Filename:=ActiveSheet.Range("B15"), Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _ ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _ (20, 1)), TrailingMinusNumbers:=True Cells.Select Cells.EntireColumn.AutoFit ActiveWorkbook.SaveAs Filename:=strFileName, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.Close End Sub -- If this post helps click Yes --------------- Jacob Skaria "Adrian" wrote: I have written the following which opens a txt file based on a location specified and then converts the format and then saves the file based on a cell address but the file is being saved using acell address on the active worksheet rather than the original one. Sub Convert() Workbooks.OpenText Filename:=ActiveSheet.Range("B15"), Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _ ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _ (20, 1)), TrailingMinusNumbers:=True Cells.Select Cells.EntireColumn.AutoFit ActiveWorkbook.SaveAs Filename:=ActiveSheet.Range("C15").Value, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.Close End Sub Can anyone help please -- Adrian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving a file using a cell for file name
Save the name before you start doing the real work. Sub Convert() dim myFileName as string myfilename = activesheet.range("C15").value Workbooks.OpenText Filename:=ActiveSheet.Range("B15"), Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _ ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _ (20, 1)), TrailingMinusNumbers:=True Cells.Select Cells.EntireColumn.AutoFit ActiveWorkbook.SaveAs Filename:=myfilename, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.Close End Sub Adrian wrote: I have written the following which opens a txt file based on a location specified and then converts the format and then saves the file based on a cell address but the file is being saved using acell address on the active worksheet rather than the original one. Sub Convert() Workbooks.OpenText Filename:=ActiveSheet.Range("B15"), Origin _ :=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _ , Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _ ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _ (20, 1)), TrailingMinusNumbers:=True Cells.Select Cells.EntireColumn.AutoFit ActiveWorkbook.SaveAs Filename:=ActiveSheet.Range("C15").Value, _ FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False ActiveWindow.Close End Sub Can anyone help please -- Adrian -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving Excel 2007 file in 2003 creates very large file | Excel Discussion (Misc queries) | |||
Saving worksheet in new file with date AND cell value as file name | Excel Discussion (Misc queries) | |||
Saving multi-tab excel file created from comma delimited text file | Excel Programming | |||
Saving a file with a name from a cell value. | Excel Programming | |||
Saving a file(new) using the multiple cell contents as a file name | Excel Programming |