"Current Directory" for Excel in vba

I have a system of workbooks that, until today, has been working fine.
I did remove an installation of DownloaderXL which is no longer in use.
Aside from all the coding nuances which I could get into, I'd rather tackle this at a system level.

There are 3 kinds of .xlsm files:
1) a large number of "identical" files which contain different data and can update themselves when run.  
They can also close after being run or stay open.
(They close after being run if run in a batch mode from a list.  They can stay open if only being run individually.)

2) a Summary file which captures data from those similar files above.
This file includes a list of the other files and can run a Macro which will open the other files one after another.

3) a Control file which provides the parameters to tell the files in #2 above to stay open or close, to update when run or not, etc.

Suddenly today the Summary file didn't know where the other files were located.
Suddenly today the listed files in #1 didn't know where the Control file was located.

CurDir for Excel is NOT the current directory for all these files - but it must have been earlier.

Finally, here are the questions:
1) What determines the Current Directory for Excel?  I'm not asking here how to set it, I'm asking what would be the default.
2) How can the Current Directory for Excel be changed? ... at the highest level possible so it stays fixed?  My objective is to change it once and for all and for it to stick while these various files are being run.
LVL 27
Fred MarshallPrincipalAsked:
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.

Glenn RayExcel VBA DeveloperCommented:
1) The Current Directory is usually the user's local Document (aka "My Documents") folder
2) It can be changed using Excel's Options (2010 Menu:  File, Options, Save)
default file location
Anytime one opens a file using the Open command in Excel, that filepath becomes the current directory, unless a VBA routine executes a ChDir command.  Even if you use a GetOpenFilename method, the last-browsed directory should remain current.

-Glenn
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
aikimarkCommented:
if you are processing different workbooks, the ActiveWorkbook.Path property (or workbook object variable.path property) is the path of the containing folder.
0
Fred MarshallPrincipalAuthor Commented:
"the containing folder"?  
Do you mean "the folder that contains the active workbook"?
That's exactly what seems to not be working.  Thus my questions....
All of the files in use here are in the same directory.
Yet CurDir is NOT that directory.

Well, I just checked the settings in Glenn Ray's answer (Thanks!) and they are as expected.  At the same time, CurDir is ALSO now as expected - tracking the current file(s) that's open.  That's all I really need but am mystified how it could have been off - even through a reboot.
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.

aikimarkCommented:
Is the workbook with the VBA code in the same directory as the target files?

I try not to use CurDir in my VBA code.
0
Fred MarshallPrincipalAuthor Commented:
All of the files in use here are in the same directory.
aikimark: you didn't answer my question re: your definition of  "the containing folder".  
You didn't say what you use instead of CurDir.
(I was using CurDir in the immediate window to see what its current value was).
0
aikimarkCommented:
I do not know the context of your use of CurDir.
I do not use DownloaderXL.

Please answer my earlier question:
Is the workbook with the VBA code in the same directory as the target files?
0
Fred MarshallPrincipalAuthor Commented:
Gee, I thought I did answer your question.
I stated the situation at the outset.
I repeated the situation in my last response:
All of the files are in the same directory

I had hoped that was clear enough to say that the answer to your question is "yes".

But, to be clearer:
*All* of the files, the data files, the control file, the summary file with the vba code, etc. etc. are in the same directory.
0
aikimarkCommented:
So, the ActiveWorkbook.Path property of the VBA code-containing workbook is the same path as all the workbooks to be processed.

If you need to change the current directory (returned by the CurDir statement), you use the ChDir statement.  It has one parameter, which is the directory you want to be considered the current directory.
Example:
ChDir = ActiveWorkbook.Path

Open in new window

If your DownloadXL (or other code) relies on the CurDir, then that is how you would change it.
0
Fred MarshallPrincipalAuthor Commented:
I didn't ask and I never did figure out *why* the current directory was different / had changed from all the weeks prior using the same files and approach.  Nor did I figure out why it fixed itself - although perhaps with some help from me.

Thanks!
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.