Temporarily altering the Options-->Save-->Save files in this format settings via VBA?

[dougmarkham]

Hi Folks,

We have an old excel model whose VBA code pw protected. The model only imports *.xls format excel files.

There is an excel template file which the operative uses to generate the import files for old model.

However, this user generates lots of excel files that have to be in *.xlsx format.

I was wondering if there is any VBA code that can change the settings in: Options-->Save-->Save files in this format?

• i.e., so that a) the VBA code runs from the opening of this template file, b) the VBA changes the default SaveAs file type to *.xls in [Options-->Save-->Save files in this format]; c) however, when the template file closes, the VBA code automatically changes the [Options-->Save-->Save files in this format] back to *.xlsx file type?

I have had 'a Google' to find out if someone else has achieved this, to no avail.
I know how to run things on wb open and wb close, but is there any VBA to change excel options? Would anybody be able to help with this issue?

Kind regards,

Doug.

 

[Norie]

Doug

Could you not change the code that generates the import files as xlsx to generate them as xls?

 

[dougmarkham]

Doug

Could you not change the code that generates the import files as xlsx to generate them as xls?

Hi Norie,

Thanks for your reply, much appreciated!

So I had to look into what other users are doing as many use similar templates saved to their local network.
It turns out that users tend to re-use a table they've already created by clearing the data within their table and pasting in new data.
They are then saving that to a bunch of different location depending upon the client, and the specific job.

Therefore, I guess what I need to ask is whether it is possible to open an excel file whose VBA runs at open to temporarily changes global default settings, so that the users transfer their data into any table template and Save As by default as *.xls, then to run another macro to return the global default SaveAs settings to *.xlsx.

I'm interested if this is even possible, if not, I'll have to drive forward a common process and as you say: code it to save as *.xls.

Kind regards,

Doug.