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
Solved

Add macros on Open

Posted on 2016-12-02
9
52 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 50

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

790 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