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
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
ASKER
Adding a workbook with the macro does not work.
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
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
ASKER
There is not a Save As option to save the file when exporting.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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