saving a file using a cell for file name

[Adrian]

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

 

[Jacob Skaria]

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

 

[Dave Peterson]

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