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

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
217
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,811
Office Version
  1. 365
Platform
  1. Windows
Doug

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

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
217
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,614
Messages
5,523,919
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top