Solved

Excel Startup Default and Alternate locations problem

Posted on 2015-02-06
26
175 Views
Last Modified: 2016-02-11
Hi,

We have a GlobalFunctions.xla that we push out to all our users pc's into their C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART that contains global functions that are available to the whole business. All works fine.

We've been bought out and are in the process of moving to the new companies infrastructure which is all thin client citrix based. As i understand it and have been told there is no AppData\Roaming available as there is no C: drive so the GlobalFunctions.xla has been specified in the Alternatate Location in the File\Options\Advanced setting within Excel. If I open Excel and hit Alt+F11 I can see that the GlobalFunctions has loaded. So far so good.

Here's the problem, Book1.xls created and saved on the old set up has a link to GetFactor (function within GlobalFunctions.xls) however, when we copy this to the new structure and open the workbook the GetFactor() is replaced with C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART!GetFactor() So it looks like it's looking for the function from the default location rather than the alternative location ??

If i remove the C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART! and save the spreadsheet it works and works each time i open it, so that leads me to believe there's some kind of internal reference to the original location of the GlobalFunctions ??

Any help appreciated.

Thanks
0
Comment
Question by:stevejebson
  • 15
  • 11
26 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40593178
When you say:


when we copy this to the new structure and open the workbook the GetFactor() is replaced with C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART!GetFactor()

Where do you see this ?
gowflow
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40593314
In the cell that's trying to access the GetFactor() function
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40593403
ok then you need to modify your .xla not just copy them to the new location.

Open the xla where it is now (and for this you should have the xls source) and make sure in your .xla (source xls) all references inside point to the new location so it is there that you need to change. then once done save the xls or xlsm and keep a copy then save as addin xla or xlam which ever. Now this file put it in the new location and you should get the correct references.

gowflow
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40597813
Sorry, i don't quite understand this. Firstly, what do you mean by open the .xla with the .xls source ?? What's the .xls source in this scenario ? I just open the .xla and access it via the vba editor (shift F11). Also, there are no refernces to either the old or new structures in the code ? i.e. no references to where the .xla is loaded from, or none that i'm aware of. I can provide some screentshots if that helps ?? Apologies fro not replying sooner, ended up in migration meetings for the rest of the day on friday.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40597879
How did you get the .xla file ? to get an .xla you create first a macro in an .xls or .xlsm then you save it as addin (.xla or .xlsam) and here you get an .xla or .xlsam and by putting this file in the XLStart this is where it then showsup as Addin in the list of Excel Addins. and by ticking on that addin it then become active and each workbook that is opened get this addin loaded automatically.

All this being said, then the question for you is to know at the beginning how you got this addin at the first place.

gowflow
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40598006
blimey, that was some time ago and gues what, it wasn't me who created it, they've now left!

But what you say sounds likely, as in it was probably created in a bog standard book1.xlsx, edited the vba to create the functions and then saved as a .xla. Only a guess but knowing the level of excel expertise here, and i include myself, not very advanced !

I must admit though my thinking on this problem was not in the way it was created, but more in the way it's been used. I was wondering if there's some kind of internal setting in a workbook or cell that links to the .xla that was originaly in the C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40598038
if there is not proprietary or confidential info could you just post the xla and I would look at it maybe I can see something ?
gowflow
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40598125
done via a msg
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40598605
ok fine let me see this.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40598896
yes I saw that in I124 it is pointing to user's path to XLSTART now I deleted it and save the workbook then its fine do you have a problem with doing this ? if you activate the Addin it will pick it up also.

Now I noticed in the Addin code that in 1 of the sub it is looking for the user path so this may be the issue but it seems you don't have the source right ? coz we can't edit the xla. we need its xls

gowflow
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40598906
hey gowflow,

I'm at home now so will check in the morning when back at work. I'll look for that path you mentioned and check out the other bits you talk about. I'll try and find that original xls too...

thanks for sticking with this, appreciate it.

Steve
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40599244
ok fine let me know what you find out.
gowflow
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40600240
Re:
yes I saw that in I124 it is pointing to user's path to XLSTART now I deleted it and save the workbook then its fine do you have a problem with doing this ?
That's exaclty the experience we have on the new infrastructure, but unfortunately due to the numbers of spreadsheets we have to move opening, removing the references and re saving are not an option at the moment.

Re:
if you activate the Addin it will pick it up also.
How do you do this ? I had a look in the Add Ins but nothing there that i could see ?

Re:
Now I noticed in the Addin code that in 1 of the sub it is looking for the user path
can you point me to this as i've looked throughout and can't see what you mean by this?

Re:
coz we can't edit the xla. we need its xls
I can't find any .xls relating to this. Why can't we edited the xla ? what am i missing here, don;t we just edit it via the VBA Editor ?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 29

Expert Comment

by:gowflow
ID: 40600269

How do you do this ? I had a look in the Add Ins but nothing there that i could see ?

You meant to say the addin does not show in the structure ?

for the rest will see after you reply to this one. I thought that the addin was showing in the new structure but you had the path issue only.

gowflow
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40600937
Might be a bit of confusion here, I thought you meant checking the Add-Ins in the Developer tab ? But now i think you mean just seeing the .xla in the tree structure within the vba editor, which i do !

As in when i open excel in the new structure and hit Alt+F11 i see VBA Project(MNPA Global.xla)

and when i open the actual .xls file copied from the old structure i get the path error.

apologies if i'm repeating myself !

(been in migration meeting all day)
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40601080
yes fine we agree now.

An other question,
Let me understand, you have lots of files similar like the one you posted (not the .xla the other one that have the path) right ? and when you fix one then the users will always open that file or what ?

I need to understand the extent of the problem.

gowlfow
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40602633
yes, we've got hundreds of xls files that will be copied to the new structure and yes, once the path is removed and saved it will be good from that point on and finally, yes they will open the same file if they need it again.

Steve
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40602643
ok I may have a solution for you but need to know where they reside these files are they all in 1 same directory ? if yes then I will need to know the full path of that directory and also can you post a couple so I can make tests and revert with feedback.

Meantime I reconstituted your xla into a source so you can keep it in your archives for later use. Unfortunately nothing in the xla indicate that it is a source of the problem I had jumped to a fast conclusion re my previous comment about finding something that point to the user location but it was a variable similar to it that have nothing to do with it.

Attached is your reconstituted xla keep it in a safe place.

gowflow
MNPA-Global.xlsm
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40605318
Hi,

I've sent you 3 different workbooks to play with. Our file structure means that there is a Root folder but then it fans out to 42 sub folders and then 3 or 4 deep within each of those folders. So i just set up a simple Root folder with a couple of sub folders and placed the 3 test workbooks within. I've sent you a msg with the workbooks just for a bit of security.

thanks

Steve
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40605996
I am testing it but blocking a bit at the password issue as it is asking for it I am trying to look if possible not to have it asked but input automatically. Will revert. So far so good.
gowflow
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40606351
ok here you go.

This file has a macro and trace and here is how it works.

Basically it is set to fix ALL your files in all your directories and subdirectories no matter how deep they are. It will look for all .xls or .xlsm files open it look for formulas and when there is a path delete it. It will put a trace of what it is doing for you.

I recommend following approach.
You put this file in a  directory that does not have any of excel files to be modified. then in that directory sake trial you create a sub directory called Temp in which you put a copy of maybe the 3 files you posted or say 10 files.

once done you run this file and run the macro inside and it will show you a trace of what it is doing and when a sheet contains nothing you will see Nil or else it will show you the new formula etc... when all files are done you will get a message.

Then once you get the message scroll onto the trace to see what happened then once done open the temp folder and check these files to see if they are ok and fixed. check the formulas check around everything.

If all is ok then run again the macro on that same directory and you will see the result.

Let me know.
gowlfow
RemPathVar.xlsm
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40607804
Sorted !! Needed a couple of tweaks to get round Read-Only files and some where there was no protection but apart from that has worked a treat.

Wish i could award more than the 500 pts, i really appreciate the time you've spent on this, really well done.

That's what makes this site so great :)

Steve
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40607835
Tks. If you give me the errors and a sample of the bumped files I can fix it. Pls do as I don't like 1/2 solution but full working ones.

gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40617260
Any news did it work for you ?
gowlfow
0
 
LVL 1

Author Comment

by:stevejebson
ID: 40618463
Hey GowFlow,

apologies for late reply, we've been getting it to work across 750+ workbooks and hit a few snags. I've attached the updated workbook and annotated our changes with comments starting ' .....

Basicaly we changed it to cater for Read-Only files, Shared Workbooks and workbooks that are provided by a 3rd party and therefore we don't have passwords.

As we're pretty inexperienced in VBA it won't be the prettiest coding i'm sure ! ;-)

Steve
RemPathVarKZB.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40618690
Noted and anything requested from me ?
gowflow
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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

758 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

19 Experts available now in Live!

Get 1:1 Help Now