Saving filename same as import filename

[Matt]

I have a macro that imports a txt file(test.txt) into an Excel spreadsheet and then I want to save this file with the same name as the txt file(test) obviously w/out the .txt. Any ideas? Thanks. Matt

 

[Tom Ogilvy]

Activeworkbook.SaveAs Activeworkbook.Path & "\" & _
left(Activeworkbook.Filename, len(Activeworkbook.Filename)-4) & ".xls", _
fileformat:=xlWorkbookNormal

--
Regards,
Tom Ogilvy

I have a macro that imports a txt file(test.txt) into an Excel spreadsheet

and then I want to save this file with the same name as the txt file(test)
obviously w/out the .txt. Any ideas? Thanks. Matt

 

[Uncle Bob]

-----Original Message-----
I have a macro that imports a txt file(test.txt) into an

Excel spreadsheet and then I want to save this file with
the same name as the txt file(test) obviously w/out
the .txt. Any ideas? Thanks. Matt

.

Dim MyFileName As String

MyFileName = "Your Text File Name"

'use the following name (using the appropriate code)
'to import text file
MyFileName & ."txt"

ActiveWorkbook.SaveAs FileName:="C:\" & MyFileName
& ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

 

[Uncle Bob]

-----Original Message-----

Excel spreadsheet and then I want to save this file with
the same name as the txt file(test) obviously w/out
the .txt. Any ideas? Thanks. Matt
Dim MyFileName As String

MyFileName = "Your Text File Name"

'use the following name (using the appropriate code)
'to import text file
MyFileName & ."txt"

ActiveWorkbook.SaveAs FileName:="C:\" & MyFileName
& ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False

.
Whoops that should be

MyFileName & ".txt"
Sorry about that..

 

[Matt]

I tried this code but I get an error that says Method 'SaveAs' of object '_Workbook' failed. I can't figure out what the problem is other than maybe it is naming the excel workbook the same as the text file it is importing. For example it is naming it test.txt.xls. Could this be a problem? fileToOpen is the name of the text file that I am importing. Thanks. Mat

ActiveWorkbook.SaveAs Filename:="H:\" & fileToOpen & ".xls", FileFormat:=xlNormal,
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False