VB in Excel.

The syntax for VB in Excel is wierd to me but the only thing I noticed is that your FileFormat argument is not one of the constants defined for the FileFormat property. try xlWorkbookNormal without quotes.

please rate my answer

I'll fiddle with it. Not sure why the underscore on your "FileFormat:=" clause.
try contacting me directly and sending the code

I can't message directly either, the VB is more than 6000 chars.

If you pm me with your e-mail i will mail you the VB.

Cheers

There is another option in the last line of the code [AccessMode as SaveAsAccessMode:=xlNoChange]
Try to use the following:

ActiveWorkbook.SaveAs Filename:=fName, FileFormat:="Microsoft Excel Workbook(*.xls), *.xls", Password:="", WriteResPassword:="", ReadOnlyRecommended:= False, CreateBackup:=False, SaveAsAccessMode:=xlNoChange

In case this does not work just paste the code here and I will suggest you.

Bye,
Shivakumar Thota
thotask6@yahoo.com

I can't really understand what your problem is, you only need the ActiveWorkBook.SaveAs Filename:=fName. The rest of the code is redundant. If you insist then FileFormat:=xlNormal should work.
This does in Excel97 and I assume in later versions.
I have presumed that you want to save an Excel spreadsheet.

Ok, so i'm still no closer to resolving this issue!

VBA is definitely not my strong point!

OK how about another try, open your workbook and goto -Tools -Options -Transition. Is the Save Excel Files as: dropdown showing "Excel Workbook" ??

Are you just trying to find the name of the active workbook, or the name of the workbook hosting the macro?
If this is the case either
fname=ActiveWorkbook.FullName
or
fName=Thisworkbook.FullName
will return the name to be saved as

I am sorry I don't understand what you are trying to do. If you are saving an Excel spreadsheet/workbook then you don't need to specify the type just saving the file is enough. Excel is linked to .xls files and recognise its own, so to speak. If you need to name a file then SaveAs with .xls address will be enough. I may have got the wrong idea about your problem.

There are at least two ways to get the proper syntax: [1] use Excel's "record macro" feature and then look at the code Excel generates for you and modify it, or [2] use the Intellisense in the VBA Editor.

The correct syntax from Excel 2000 follows. Note the use of the "xlNormal" constant. It's important to use the built-in constants in VBA.

ActiveWorkbook.SaveAs _
Filename:="C:\junk.xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

OK, I think I give up
Why are you using the Application.GetSaveAsFilename. If all you want is the name of a workbook then an earlier suggestion I made will do that. If you already know the name of your workbook then a simple activeworkbook.save will save it to its original location. If you want to rename it and change the path, this can be done in the SaveAs command you are using. You seem to be complicating something.

You haven't said, but do you include the ".xls" extension in your filename? I have a rather long Excel macro that also "Saves As" & it works for me. I also have the FileFormat set to xlNormal.

And as for using a date cell for your filename, convert it to a string & use the Mid function to set the date order to the way you want it (don't forget to include the .xls extension).

Your problem is because you are using the GetSaveAsFilename dialogue box, which defaults to All Types because it is used to enable your user to changea file name or overwrite a previous file. If you already know the file name that you want to save as, then code this name and use the SaveAs command that does not require user input.
But I may still have the wrong idea about your problem and requirements

As you are using "Application.GetSaveAsFilename" you have to add the "fileFilter"

Try the ammended commands.

Do
fName = Application.GetSaveAsFilename(fileFilter:="xls Files (*.xls), *.xls")

Loop Until fName <> False

ActiveWorkbook.SaveAs Filename:= _
fName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Regards

OK, you said: "The filename is variable and i cannot run it from a cell as it is a date and tries to use '/' as the interval instead of the value i specify."

If you use a forward slash in a filename, the operating system will interpret this as the name of a folder in the path to the file. Try using the FORMAT function to come up with a date-based filename that doesn't use forward slashes.

sFilename = Format(dMyDate,"mm-dd-yyyy") & ".xls"

This question was closed by the author