通过 VBA 更改 excel 选项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12249613/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
change excel options via VBA
提问by lorenz albert
Since some people got me wrong: this is not about saving. The problem is another one ;)
因为有些人误会了我：这不是关于储蓄。问题是另一个;)
I am working with excel 2007 and have a problem. In my macro I have to assure that one option is set correctly. the option is as follows: office Symbol -> excel-options -> save and then the first dropdown menu/combobox is regularly set to ".xls" but it has to be ".xlsx" Is there any VBA command that solves this prob? A workaround could be that I write a batch file to change the regedit but I don't want to use external code.
我正在使用 excel 2007 并且遇到了问题。在我的宏中，我必须确保正确设置了一个选项。选项如下：office Symbol -> excel-options -> save 然后第一个下拉菜单/组合框定期设置为“.xls”但它必须是“.xlsx”有没有解决这个问题的VBA命令? 解决方法可能是我编写了一个批处理文件来更改 regedit，但我不想使用外部代码。
edit: @hydev Ok the problem is. That I am copying several sheets from another excel file and do lots of operations. The original excel file's format is .xlsm and if i open a new workbook it is opened as .xls (due to this config) but it is not allowed to copy sheets from .xlsm or .xlsx to .xls because the possible range of columns and rows of .xls is much smaller.
编辑：@hydev 好的，问题是。我正在从另一个 excel 文件复制几张工作表并执行大量操作。原始 Excel 文件的格式是 .xlsm，如果我打开一个新工作簿，它会以 .xls 格式打开（由于此配置），但不允许将工作表从 .xlsm 或 .xlsx 复制到 .xls，因为可能的列范围.xls 的行要小得多。
And I cannot tell excel that I want it to be .xlsx
而且我无法告诉 excel 我希望它是 .xlsx
回答by lorenz albert
Wow I used the macro recorder and it worked. Thought it only works for excel settings that are non-global. The solution to my own question is.
哇，我使用了宏记录器，它工作正常。认为它仅适用于非全局的 excel 设置。我自己问题的解决方案是。
Application.DefaultSaveFormat = xlOpenXMLWorkbook
to set save format to .xlsx
将保存格式设置为 .xlsx
回答by Mikey Mouse
There's a Save AsFunction you could use to choose the type they save the file as (It looks like 51 is the one you want)
有一个另存为功能，您可以用来选择他们保存文件的类型（看起来 51 是您想要的类型）
Then you'd just need to override the Save call to call your SaveAs Function with the File Format you want.
然后，您只需要覆盖 Save 调用即可使用所需的文件格式调用 SaveAs 函数。
Edit:
编辑：
Here's a sample of someone overriding the save event(the second reply)
这是某人覆盖保存事件的示例（第二个回复）
回答by datatoo
Your later edit indicates that the starting format is not xls, but rather xlsm, which means it is macro enabled.
您稍后的编辑表明起始格式不是 xls，而是 xlsm，这意味着它启用了宏。
Excel 2007 and up will force a file with a macro to have an xlsm extension. You probably are not going to get around it's insistence upon that.
Excel 2007 及更高版本将强制带有宏的文件具有 xlsm 扩展名。你可能不会绕过它的坚持。
If you truly need to save it as an xls extension you will have to remove the macro as part of your save process.
如果您确实需要将其保存为 xls 扩展名，则必须在保存过程中删除该宏。
回答by hydev
Can I ask the context? The reason is you can set the SaveAs to equal xlsx in VBA. Therefore you could create a button that enabled the user to save to your requirements.
可以问上下文吗？原因是您可以在 VBA 中将 SaveAs 设置为等于 xlsx。因此，您可以创建一个按钮，使用户能够根据您的要求进行保存。
Mike
麦克风
