Excel opens from .xls file without PERSONAL.xls available - how to overcome this?

I have a database program that will "print" (ie. export) query results to an Excel file.  Problem is that the instance of Excel that this database opens does not have access to PERSONAL.xlsb, so I can't run any of my macros on the generated .xlsx file.  I have to save the file,  restart Excel, and re-open the file to use PERSONAL.xlsb.

How do I overcome this so Excel has access to my macros when the file is created?
mycomacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtCommented:
PERSONAL.xlsb will be automatically opened if it is in the XLSTART folder. It will also be opened if you put it in a folder specified in the File...Options...Advanced tab in the "At startup, open all files in:" field (about 80% down the dialog). Try putting the path to your PERSONAL.xlsb macro in that field in the instance of Excel that opens when the database exports the query results.

Alternatively, create a macro in that other instance and make sure that it is saved to its PERSONAL.xlsb workbook. Edit that macro so it contains the following statement:
MsgBox ThisWorkbook.FullName
When you close Excel, make sure that the newly created PERSONAL.xlsb is saved. Then do the database program "print" operation once again and see where the PERSONAL.xlsb is located. You can then copy your macros into that other PERSONAL.xlsb
0
mycomacAuthor Commented:
When I try to record a macro to Personal  I get an error message "Personal Macro Workbook in the startup folder must stay open for recording".  Click OK, get "Unable to record" error.

This instance of Excel was started by the .xlsx output from the database program.
0
mycomacAuthor Commented:
When I try to set the startup file path in Options - Advanced, the changes are not saved.  
- They aren't saved to the instance of Excel that launches when starting Excel normally,
- or by starting Excel from the database.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

byundtCommented:
That sounds like policy settings are preventing Excel from writing to the registry.

Is the instance of Excel that opens up a network version of Excel? If so, perhaps you could talk to your network admin to get PERSONAL.xlsb where it needs to be.
0
mycomacAuthor Commented:
No network involved.  This all happens on my PC.  The db software that generates the file and opens the restricted version of Excel is based on FoxPro I think.
0
byundtCommented:
Do you have Admin privileges on your computer? If not, consider acquiring them so you can get the database-launched version of Excel to use your PERSONAL.xlsb. You can revert back to non-Admin status once that has been accomplished.
0
mycomacAuthor Commented:
I'm the only user, and the administrator
0
byundtCommented:
Perhaps this is a problem that can be solved from the database side of things. I suggest that you add it to the topic areas this question is seen in.

If the database program is running a VBA macro, could you post that?
1
Jacques Bourgeois (James Burger)PresidentCommented:
Do you have code in the ThisWorkbook module of Personal.xslb? There might be code in the Open event that close the file or does something else so that it is not accessible.
0
mycomacAuthor Commented:
This was solved on the software that launched Excel.  Needed a switch when excel started that allowed access to the personal.xls.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mycomacAuthor Commented:
This was solved on the software that launched Excel.  Needed a switch when excel started that allowed access to the personal.xls.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.