PDA

View Full Version : If XLSM in XLSTART, Then Excel doesn't open with blank WB



Paul_Hossler
03-24-2016, 11:16 AM
In Excel 2013 and 2016, if there is an XLSM file in XLSTART, then just opening Excel.exe from a 'standard' shortcut does not open with a blank Book1 (like having /n on the command line)

Removing all XLSM files, then opening Excel.exe does open with a blank Book1, or saving the XLSTART files as XLSB does open with a blank workbook

Double clicking a Excel file opens it normally

If the Excel shortcut uses Excel /t "....Templates\book.xlxt" and there are XLSM files in XLSTART, then Excel opens with a blank book1 (based on the formats, etc. in book.xltx)

The Option "Show the Start screen when this application starts" is NOT checked

Some users want to open Excel and have a blank workbook (book1) all ready to go.

Is there a setting or workaround to allow users to have XLSM files in XLSTART and to also have Excel open via shortcut to have a Book1?

SamT
03-24-2016, 12:51 PM
Why have an xlsm in xlStart? (as opposed to an xlsa file.) To provide common Macros for everybody using that computer without having to convert it to an addin.

However, Personal.xlsm would do the same thing and would not interfere with Excel's normal operation like an xlsm file in xlStart does.

Paul_Hossler
03-24-2016, 01:33 PM
Why have an xlsm in xlStart? (as opposed to an xlsa file.) To provide common Macros for everybody using that computer without having to convert it to an addin.

However, Personal.xlsm would do the same thing and would not interfere with Excel's normal operation like an xlsm file in xlStart does.

1. Because I want the workbooks (which are hidden) to open every time I start Excel. Each WB is a group of related macros and not all users have all WBs, depending on role.

2. What is an xlsa file? (typo?) XLSB files seem to allow an blank Book1 when you open Excel. Are you saying that they should be saved and edited as XLSB files? ... Hmmmmm

Edit - that only seems to work for PERSONAL.XLSB, but if there are any other files (even xlsb's) there is no Book1 when you open

3. If I have personal.xlsm in c:\users\me\appdata\roaming\microsoft\excel\XLSTART, users don't get the opening blank workbook.

4.Where else would I put personal.xlsm? I've always had it in XLSTART, but might not be where it should go

5. I could easily make them addins, but it is oft times easier to unhide a wb, make some changes (to the macros or to the worksheets), and hide it. Closing Excel gives me the prompt to save (keeping it's hidden state)

SamT
03-24-2016, 02:32 PM
How about something like

OpenTime= Now
Do Until Abs(DateDiff("s", Opentime, Now)) > 1
Do Events
Loop
If Workbooks.Count = 1 Then Workbooks.Open(Standard Blank book)

snb
03-25-2016, 03:11 AM
Since I found xlsb I never use xlsm anymore.

Why do yo want to save them as xlsm ?
Which method do you use to make then 'hidden'

I also do not understand why you want to avoid addins ?

Paul_Hossler
03-25-2016, 04:59 AM
1. I'm learning more about XLSB's, but I've fixed XLSM files by unzipping them and editing the XML. That and habit are probably the only reason

2. With the XLSM WB open, View, then Hide. When you exit Excel, you get a prompt to Save. That keeps the hidden state. There's also Show and Hide macros, since sometimes the user needs to make ad hoc changes to the worksheet (database, control parms, options, etc.). Typically the hidden XLSM's are used to distribute a group of macros to users. They just save to XLSTART and run a macro via Alt-F8, or by adding to their QAT

3. The add-in requires the macro developer (not myself) to add and maintain CustomUI XML (not hard but he doesn't want to), but also doesn't allow the user to make 'data base' changes to the worksheets


The bigger question is "Why does Excel open without a Book1 if there is anything other than PERSONAL.XLSB in XLSTART?"

Saving what used to be my PERSONAL.XLSM as PERSONAL.XLSB in XLSTART will allow Excel to open with Book1

Adding another XLSB with a group of the distributed macros, prevents a Book1 (which for some reason people don't want) when you start Excel from a shortcut

snb
03-25-2016, 06:45 AM
To quickly make a workbook hidden:


Sub M_snb()
With GetObject("G:\OF\example.xlsb")
.Save
.Close 0
End With
End Sub

What if you use a link in XLstart to the xlam or the xlsb (stored elsewhere).
Maybe the opening workbook may appear.