Solved

Excel 2003 does not access Personal.xls when workbook initiated (created) from other program

Posted on 2014-09-13
10
131 Views
Last Modified: 2014-11-21
I have a software scheduling program that exports report data to Excel.  I need access to a macro stored in my personal macro workbook that formats the report data.  When I export the data to Excel this way, the personal macro workbook is not available in the instance of Excel that the workbook is opened in.

I have designated the path to personal.xls in the Tools>Options>General>At startup, open all files in: dialog, but to no avail.  If I start Excel from the icon (normally), personal.xls loads fine, but it doesen't if launched when exporting from the other program.  The only macros I have access to in the exported file are All Open Workbooks, This Workbook, and Book1.   The Window>Unhide menu item is grayed out.

It doesn't matter if Excel is already running or not when I export to Excel.

Thanks for your help.

Jeff
0
Comment
Question by:mycomac
10 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40320977
You could create a new, blank workbook with your relevant code, save it in the XLSTART directory and see if that will make the macro available.

Since your other software is launching a new instance of Excel, I don't know if this will work.  The other alternative is to check the software registry settings to see if there is a command string for starting Excel, and if so, add a command to open the Personal.xls workbook.  I'm posting mobile, so I don't recall the correct command line switches.

Regards,
-Glenn
0
 

Author Comment

by:mycomac
ID: 40321092
Adding a workbook with the macro does not work.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40322928
When exporting the data is there an option to Save As rather than open with Excel?

You could then Save the data and open it in Excel as normal.

I know this doesn't really answer the question but could be a workable solution.

Alternatively, if you have two sessions of Excel open; one opened normally (therefore including your Personal.xls) and the session created by the export you should be able to copy and paste the excel data from the export generated session into a blank workbook in the the normal session.

Thanks
Rob H
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:mycomac
ID: 40341523
There is not a Save As option to save the file when exporting.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40341561
I have realised, since my original posting, that I have the same issue when exporting a report from a Java platform.

The Export to excel creates a new session (with two files for some reason). I have to copy and paste the data into an existing session before I can run any existing routines on it.

However, because it is copying from an excel grid and pasting to an excel grid it is just a question of copy and paste, I don't have to do any paste special. I don't have any formatting on the sheet where the data is being pasted but I guess if I did have it would be overwritten.

Would this be an issue for your scenario?

The way round would be to copy from the report generated session, paste into a blank sheet in the proper session, copy from this sheet and then paste values into the workbook that needs it.

Thanks
Rob H
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40341624
Just thought of another alternative and it seems to work in my situation.

Although the report generated instance of Excel does not open your Personal.xls, you can open it manually in that instance. You will get a Read Only warning as it is already open in the other instance of Excel.

I have just managed something which might be a bit of a workaround. I have added an icon to my Quick Access Toolbar which opens my Personal.xls workbook. When the report generated instance is created, the icon appears on the QAT as normal. Clicking it opens the Personal.xls with a Read Only message, twice for some reason.

Thanks
Rob H
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40341635
Alternatively, rather than opening the Personal.xls, direct the QAT icon to a workbook containing the VBA scripts pertinent to the report that you are working on.

However, thinking about it, you would still get one Read Only message as it would have to open the Personal.xls workbook in order to run the macro to open the other workbook.

Thanks
Rob H
0
 

Author Comment

by:mycomac
ID: 40343836
Your last line of thinking by opening PERSONAL.XLS has led me in a direction that may be an acceptable work-around using a toolbar button.

I created a macro to open personal.xls,  saved the macro in book1.xls, and saved book1.xls to my XLSTART folder, then assigned that macro to a toolbar button.  

I had to set my macro security to none to get rid of the "macros can be dangerous...disable / enable" nag screen, but it works.  If there is some other way to get around the nag screen without setting security to none, i'd be interested, but it seems that checking the "trust macros in this workbook" box in macro security settings doesn't apply to any workbooks in XLSTART.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40456939
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

813 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

18 Experts available now in Live!

Get 1:1 Help Now