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

[JeffK]

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

 

[Ron de Bruin]

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

 

[Jim Rech]

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.

--
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
| 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

 

[Ron de Bruin]

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" >

<!-- Add one button and a menu with 7 options to the Office Button menu-->
<!-- Add one button to the Print splitButton and one to the Prepare menu-->
<!-- See how I use &amp; to have a underlined letter in the menu name (see label)-->
<!-- Click on the "generate Callbacks" button in the UI editor to create the macro(s)-->
<!-- You can copy them in a module in your workbook then and add your code-->

<ribbon>
<officeMenu>

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

<menu id="MyDropdownMenu" label="&amp;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>

 

[Ron de Bruin]

For the OP:
The name of the menu is

<menu idMso="FileSaveAsMenu">

 

[Jim Rech]

Excellent, Ron. I certainly stand corrected!

--
Jim
|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" >
|
| <!-- Add one button and a menu with 7 options to the Office Button
menu-->
| <!-- Add one button to the Print splitButton and one to the Prepare
menu-->
| <!-- See how I use &amp; to have a underlined letter in the menu name
(see label)-->
| <!-- Click on the "generate Callbacks" button in the UI editor to create
the macro(s)-->
| <!-- You can copy them in a module in your workbook then and add your
code-->
|
| <ribbon>
| <officeMenu>
|
| <button id="customButton1" label="&amp;Your Macro" onAction="Macro1"
imageMso="DirectRepliesTo" />
|
| <menu id="MyDropdownMenu" label="&amp;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>
|
|
|
| --
|
| 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
| > 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.
| >
| > --
| > 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
| > | 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
| >
| >

 

[JeffK]

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

 

[Ron de Bruin]

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

 

[JeffK]

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