Saving a file

[Guest]

I have the following statements:

DataFileName = Application.GetOpenFilename(fileFilter:="Text or ASC Files,
*.txt; *.asc", Title:="Select the Data File")

....code

'Saves The File (As Sheetx) And Leaves It Open
ActiveWorkbook.SaveAs

'Saves File As DataFileName But With The Extension Of the File Opened TXT
Format And Not In Excel Format
ActiveWorkbook.SaveAs (DataFileName)

'Receive Syntax Error
ActiveWorkbook.SaveAs (DataFileName, FileFormat:="Excel (*.xls), *.xls")

What am I missing?

 

[Dave Peterson]

fileformat is not like the filefilter--where you want to give a choice.

You tell excel the format you want to use:

ActiveWorkbook.SaveAs DataFileName, FileFormat:=xlworkbooknormal

See VBA's help for all the different fileformats you can use.

 

[Guest]

This does not work. Excel still wants to save the file as DataFileName type
and overwrite the txt file and not create a "xls" file.

 

[Dave Peterson]

You sure? Try opening that file in Notepad--I bet it looks greek to you.

It'll overwrite the existing file name with a real excel workbook--but that
workbook has an extension of .txt (or .asc).

If you want to strip the last 4 characters from the name, you can use:

datafilename = left(datafilename,len(datafilename)-4) & ".xls"

ActiveWorkbook.SaveAs DataFileName, FileFormat:=xlworkbooknormal

And you'll have a file that ends with .xls.

 

[Guest]

Without the xls extension it would not have been intuitive to others that the
file can be opened by excel. Also if the original data was needed (without
modifications) this would be lost.

Thanks I had just finished implementing the statement:

datafilename = left(datafilename,len(datafilename)-4) & ".xls"

when you post appeared.

 

[Dave Peterson]

Oh, I agree with your assessment. I wouldn't change the extension of a normal
workbook from .xls unless I had a very, very good reason.

But when I first read your initial post, I focused like a laser beam <vbg> on
the fileformat portion--not the extension portion.

Without the xls extension it would not have been intuitive to others that the
file can be opened by excel. Also if the original data was needed (without
modifications) this would be lost.

Thanks I had just finished implementing the statement:

datafilename = left(datafilename,len(datafilename)-4) & ".xls"

when you post appeared.