Web Development

General discussion

Gravatar
0 Votes
Locked

VB in Excel.

By Frenchwood ·
Hope someone can help me on this.

I have written a VB macro in Excel that does a variety of things, and the final one being to save the file.

What i am trying to do is set the fileformat, however; regardless of the coding i always end up with Save as type "All Types(*.*)".

My VB is as follows:


Do
fName = Application.GetSaveAsFilename
Loop Until fName <> False
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:="Microsoft Excel Workbook(*.xls), *.xls", Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False


Any clues?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

gravatar
Collapse -

by dryflies In reply to 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

gravatar
Collapse -

by Frenchwood In reply to

I have tried the code Format:= _ xlWorkbookNormal which still left the file saving as an 'All files (*.*)', so i have changed the format to what is specified in the 'Save As Type' box.

Any other ideas?

I would paste the full VB but it is more than 2000 chars

gravatar
Collapse -

by dryflies In reply to VB in Excel.

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

gravatar
Collapse -

by Frenchwood In reply to

Can't PM VB - It's too long.

See my above comment.

gravatar
Collapse -

by Frenchwood In reply to

Can't PM VB - It's too long.

See my above comment.

gravatar
Collapse -

by Frenchwood In reply to VB in Excel.

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

gravatar
Collapse -

by thotask6 In reply to VB in Excel.

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

gravatar
Collapse -

by Frenchwood In reply to

Thotask6, Your method gave a VB compile error.

I can't post the VB as it is over 6000 chars long.

gravatar
Collapse -

by kpennington In reply to VB in Excel.

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.

gravatar
Collapse -

by Frenchwood In reply to

That's right, but the file attempts to save as (*.*) regardless of whether or not the "FileFormat:= " is specified.

It quite perplexing as the default should be *.xls, hwever Excel seems to think it can save as all types!

Back to Web Development Forum

Start or search

Related Discussions

Related Forums