Solved

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

Posted on 2014-09-13
10
127 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
Comment Utility
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
Comment Utility
Adding a workbook with the macro does not work.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
 

Author Comment

by:mycomac
Comment Utility
There is not a Save As option to save the file when exporting.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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 …
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…

763 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

8 Experts available now in Live!

Get 1:1 Help Now