Adding format to "save as" sub-menu in Office 2007?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JeffK - 24 Apr 2008 19:14 GMT

I save all my files as either .xlsx or .csv. Usually I need to save
the same file as both - once to preserve features such as charts and
once in .csv for import into another application.

Is there a way to add a format to the "save as" window? That is, I
would like to be able to click the office button, click "save as", and
have .csv appear in the list of formats alongside "Excel workbook",
"Excel macro-enabled workbook", etc.

Granted, I can choose Save As>Other formats and scroll down to .csv in
the save dialog, but I do this often enough that it would be quite
helpful to save that step.

Thanks for any help,
Jeff

Reply to this Message

Ron de Bruin - 24 Apr 2008 19:34 GMT

Hi JeffK

You can create your own macro button in the Office menu
There are examples on this page
http://www.rondebruin.nl/ribbon.htm

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

>I save all my files as either .xlsx or .csv. Usually I need to save
> the same file as both - once to preserve features such as charts and
[quoted text clipped - 11 lines]
> Thanks for any help,
> Jeff

Reply to this Message

Jim Rech - 24 Apr 2008 20:00 GMT

I'm not sure that we can add another file type to the Save As right pane.
There must be a way to do it because it looks like the "PDF or XPS" add-in
adds one there. But I don't think MS has documented it. Or I'm just spacey
today. Anyway you may have to add an item to the Office button menu proper
or to the QAT, and link it to a macro that creates a CSV or opens the Save
As dialog with CSV defaulted to.

Signature

Jim

|I save all my files as either .xlsx or .csv. Usually I need to save
| the same file as both - once to preserve features such as charts and
[quoted text clipped - 11 lines]
| Thanks for any help,
| Jeff

Reply to this Message

Ron de Bruin - 24 Apr 2008 20:13 GMT

I have a example in the download with XML files for the Sample menu in the UI editor for the Print and Prepare menu

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" >







<ribbon>
<officeMenu>

<button id="customButton1" label="&Your Macro" onAction="Macro1" imageMso="DirectRepliesTo" />

<menu id="MyDropdownMenu" label="&My Menu" imageMso="HappyFace" >
<button id="customButton2" label="Caption 2" onAction="Macro2" imageMso="TextAlignGallery" />
<button id="customButton3" label="Caption 3" onAction="Macro3" imageMso="TextAlignGallery" />
<button id="customButton4" label="Caption 4" onAction="Macro4" imageMso="TextAlignGallery" />
<button id="customButton5" label="Caption 5" onAction="Macro5" imageMso="TextAlignGallery" />
<button id="customButton6" label="Caption 6" onAction="Macro6" imageMso="TextAlignGallery" />
<button id="customButton7" label="Caption 7" onAction="Macro7" imageMso="TextAlignGallery" />
<button id="customButton8" label="Caption 8" onAction="Macro8" imageMso="TextAlignGallery" />
</menu>

<splitButton idMso="FilePrintMenu">
<menu>
<button id="customButton9" label="Caption 9" onAction="Macro9" imageMso="FilePrint" description="Your text"/>
</menu>
</splitButton>

<menu idMso="FilePrepareMenu">
<button id="customButton10" label="Caption 10" onAction="Macro10" imageMso="HappyFace" description="Your text"/>
</menu>

</officeMenu>
</ribbon>
</customUI>

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> I'm not sure that we can add another file type to the Save As right pane.
> There must be a way to do it because it looks like the "PDF or XPS" add-in
[quoted text clipped - 18 lines]
> | Thanks for any help,
> | Jeff

Reply to this Message

Ron de Bruin - 24 Apr 2008 20:24 GMT

For the OP:
The name of the menu is

<menu idMso="FileSaveAsMenu">

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

>I have a example in the download with XML files for the Sample menu in the UI editor for the Print and Prepare menu
>
[quoted text clipped - 57 lines]
>> | Thanks for any help,
>> | Jeff

Reply to this Message

Jim Rech - 24 Apr 2008 20:51 GMT

Excellent, Ron. I certainly stand corrected!

Signature

Jim

Reply to this Message

JeffK - 24 Apr 2008 22:05 GMT

Thanks guys, appreciate the help.

Unfortunately my VB skills are fairly limited. I wasn't able to use
the "record macro" feature, as I am unable to stop the macro when the
Save as... dialog is open. I was able to generate a macro to save the
current workbook, the only issue is that in looking at the VB code the
filename is "hardcoded". i.e., whatever name the file was saved as
while recording the macro is always going to be used.

I guess the way around this is to have the macro check what the
current filename is, and then do the "save as..." with that filename.
Maybe I'll get around to that someday...

Thanks,
Jeff

Reply to this Message

Ron de Bruin - 24 Apr 2008 22:24 GMT

The basic code looks like this if you want to change the name

Sub Test()
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook
fname = Application.GetSaveAsFilename("", _
fileFilter:="CSV Files (*.csv), *.csv")
If fname <> False Then
Wb.SaveAs fname, FileFormat:=6
Else
'Do nothing
End If
End Sub

If you always want to use the same name as the Excel workbook post back
It is bedtime for me but I am sure Jim will read it

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

> Thanks guys, appreciate the help.
>
[quoted text clipped - 11 lines]
> Thanks,
> Jeff

Reply to this Message

JeffK - 24 Apr 2008 22:34 GMT

Great, thanks again.

I'm little embarassed to display my pitiful coding skills, but this is
doing just what I need:

a = ActiveWorkbook.Name
d = ActiveWorkbook.FileFormat
b = Len(a)
If d = 51 Then c = Left(a, b - 4) Else c = Left(a, b - 3)
ActiveWorkbook.SaveAs Filename:= _
c & "csv", _
FileFormat:=xlCSV, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Thanks a lot for the help. I've now got a shortcut to the macro on the
QAT and life is good.

Jeff

Reply to this Message