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.