A Macro to Open a New Workbook, 
then Hide and Save it
  Back to Macro 
List of Topics
 
  Home
There times when you desired to create a new workbook, and then save it as a hidden file. But once you hide a file, it is difficult to save the file as it is not the active workbook. 

The manual procedures for this would be: 

  • Click on New Workbook 
  • Add your data
  • Hide the file

    • Windows Hide (in the menu)
  • Exit Excel, responding Yes to saving the changes
Using Excel's Save/Save As commands only allow you save the active workbook, and since the file is hidden, it is not active. Most of the related VBA commands work the same way. 

The following macros will allow you to open a new workbook, do something in the workbook, hide it, then save it. 
 

    Sub HideAndSave() 
      1    Set Newbook = Workbooks.Add 
      2    Newbook.Activate 
      3    DoSomething 
      4    ActiveWindow.Visible = False 
      5    Newbook.SaveAs filename:="defaultstuff.xls" 
    End Sub 
The line numbers are for reference only, do not add them when entering this macro. 

Line 1--Adds a new workbook and "Sets" it with a name of Newbook. Any valid name can used in place of Newbook 
Line 2--Ensures the workbook is active 
Line 3--A subroutine "doing something" to the file 
Line 4--Hides the ActiveWindow, which is the new workbook 
Line 5--Saves the (hidden) workbook with the name provided 

By setting the new workbook with a name (Newbook), line 5 is very specific about what workbook to save. Remember, the file must be hidden when saved in order for Excel to remember that it should be hidden (if it's hidden when saved, it will be hidden the next time it's opened. And since it is hidden, the usual commands such as ActiveWorkbook.Save will not work. 
(The name that was set is actually a variable.) 

Additional Comments:
You may need to add a line to change to the appropriate directory before saving it.  The nature of such a file is similar to that of the Personal.xls workbook, therefore, it will most likely be stored in the XLStart folder.

Why would someone need do this?
This macro arose from the need to set up some default information available to the user.  It is an Excel workbook, that has certain information the user needs, settings of the user's system, lookup tables and the like. The instructions to the user are to open the file, run a macro, which does certain things, then saves itself (hidden) on the user's PC. The user doesn't know this hidden file exists.  The macro was assigned to Ctrl+Shift+S and disables itself after it is run. 
 

This macro will work in all Versions of Excel, beginning with Excel 5.  However, do not use a long file name if you are using Excel 5. 
 
 
 

Back to Top

 
 
 
Top

 
 
 
 
 
 
 
 
 
 
 
 

 

solutions@info-stat.com