?
Solved

"Current Directory" for Excel in vba

Posted on 2014-07-23
9
Medium Priority
?
1,692 Views
Last Modified: 2014-07-24
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.
0
Comment
Question by:Fred Marshall
  • 4
  • 4
9 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 1200 total points
ID: 40215684
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40216054
if you are processing different workbooks, the ActiveWorkbook.Path property (or workbook object variable.path property) is the path of the containing folder.
0
 
LVL 26

Author Comment

by:Fred Marshall
ID: 40216842
"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
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.

 
LVL 46

Expert Comment

by:aikimark
ID: 40216848
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
 
LVL 26

Author Comment

by:Fred Marshall
ID: 40217139
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
 
LVL 46

Expert Comment

by:aikimark
ID: 40217193
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
 
LVL 26

Author Comment

by:Fred Marshall
ID: 40217773
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
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 800 total points
ID: 40217784
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
 
LVL 26

Author Closing Comment

by:Fred Marshall
ID: 40217911
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

840 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