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

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
mycomacAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
mycomacAuthor Commented:
Adding a workbook with the macro does not work.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Rob HensonFinance AnalystCommented:
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
 
mycomacAuthor Commented:
There is not a Save As option to save the file when exporting.
0
 
Rob HensonFinance AnalystCommented:
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
 
Rob HensonFinance AnalystCommented:
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
 
mycomacAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.