Solved

Add macros on Open

Posted on 2016-12-02
9
63 Views
Last Modified: 2016-12-04
Hello,

I have a list of macros in an excel file (also have them in a .bat file)
When I open excel, I want these macros to open as well so I can use them.

I know I can add the excel file to the C:\Users\<your username>\AppData\Roaming\Microsoft\Excel\XLSTART. but that opens the file with the list of macros.  
I do not want the file to open.

How can I have access to these macros "on open" of excel without seeing the file or is there something similar in excel like MS WORD has in the Normal template?
0
Comment
Question by:pdvsa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 41909903
Hi,

One way to do it is to find the book.xltx file add the macros save it as xltm then whenever xl starts you will have the macros in the "normal" template
or you use the workbook_open event of your file to hide it

 Private Sub Workbook_Open()
Me.Windows(1).Visible = False
End Sub

Open in new window

Regards
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41909949
Put the macros into the Personal.xlsb file which is stored in the XLSTART directory. Once you have the macros in the file, use the View ribbon to Hide the file. Closing Excel will then ask if you want to Save your Personal file and you need to say yes. When opening Excel the personal file will open but will be hidden.

If you can't find the Personal.xlsb it probably hasn't been created. Use the Macro recorder to record a very simple macro. In the initial popup for the recorder use the "Store macro in" drop-down to choose Personal Macro Workbook. Simple macro can be as simple Start recording, select a cell, stop recording. Once the Personal has been created this simple macro can be deleted.

Thanks
Rob H
0
 

Author Closing Comment

by:pdvsa
ID: 41909953
Ahh yes i seem to remember this.  I do also remember that I have not setup the personal excel file.  I will do this.

Very nice.

Thank you!
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41910385
My personal preference to the PERSONAL workbook is create an Excel addin. You have more control over this and can easily transfer it to another computer or user
0
 

Author Comment

by:pdvsa
ID: 41911883
Rob, I have created the macro and stored it in the personal.xlsb
However, I still do not see the personal.xlsb in the following path:
C:\Users\johnsoat\AppData\Roaming\Microsoft\Excel\XLSTART

I did verify that the macro was created and is stored in the "personal.xlsb"
I also unhid the personal.xlsb

Do you happen to know why the file doesn't appear in the XLSTART?
I am on a work computer but I can access the path.
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41911902
The Personal workbook is hidden by default to prevent accidental deletion or changes.

In the View Tab of the Ribbon you will see the Windows group. In that group is a button labelled unhide, click it and a dialog listing hidden workbooks should be displayed. Select personal.xlsb and click OK.

 

You shouldn't need to do this except to edit the macro. When finished hide the workbook again
0
 

Author Comment

by:pdvsa
ID: 41911909
HI Roy, thanks for the response.   I am still a little puzzled because I did unhide the file but its not showing.  Please see screenshot of the personal.xlsb file showing that it is unhidden (its greyed out).

Personal.xlsb
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41911918
Looking at the screen shot it appears that Personal.xlsb is actually unhidden. What happens when you look in the VB EDitor, can you see the code.

Click File in that workbook's Ribbon and on the left click "Open file location", see which folder is opened
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41911999
In Explorer go to Folder options and one of the options will be to hide system files. Untick that and the file should appear. Don't know why you would need to do that though???
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question