Solved

Add macros on Open

Posted on 2016-12-02
9
49 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
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 49

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 32

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 18

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 18

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 18

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 32

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

773 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