change excel options via VBA

0
votes

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.

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.

And I cannot tell excel that I want it to be .xlsx

4
excelvba
does this not work for you? Dim oBook As Workbook = _oExcel.Workbooks.Open("C:\Users\ThisUser\Documents\Data.xlsx", , False) – hydev
Ah, so it's more about opening the files in that format than saving them. – Mikey Mouse
Sorry @lorenzalbert maybe I'm mis-understanding you. If your using VBA you can subsitute the name of the Excel file into the Workbooks.Open command. This will then open the Excel file as a .XLSX and you can read from it as you need. – hydev
There is no file that exists at runtime that needs to be .xlsx but the macro is adding a new workbook and this workbook has to be .xlsx I tried so many different things to make it work. Another example it doesn't work if I add a workbook and save this as .xlsx it is still being treated as .xls – lorenz albert

4 Answers

2
votes

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.

Application.DefaultSaveFormat = xlOpenXMLWorkbook

to set save format to .xlsx

1
votes

There's a Save As Function you could use to choose the type they save the file as (It looks like 51 is the one you want)

Then you'd just need to override the Save call to call your SaveAs Function with the File Format you want.

Edit:

Here's a sample of someone overriding the save event (the second reply)

0
votes

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.

Mike

0
votes

Your later edit indicates that the starting format is not xls, but rather xlsm, which means it is macro enabled.

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.

If you truly need to save it as an xls extension you will have to remove the macro as part of your save process.