Solved

Add macros on Open

Posted on 2016-12-02
9
44 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
 
LVL 17

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 17

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 17

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now