Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-09-13
10
Medium Priority
?
140 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
[X]
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
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 33

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

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 33

Accepted Solution

by:
Rob Henson earned 2000 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 33

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 49

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

664 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