MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Problem with Excel VBA Creating Subdirectory

  Asked By: Hondo    Date: Nov 30    Category: MS Office    Views: 2605

I have a program in VBA that I use quite regularly. In the program I
first check for a particular Subdirectory and that works fine if the
directory exists. Now, suddenly, if the directory does not exist I
try to create it using the code as follows:

sub NewDirectory
ChDrive "C:/"
Mkdir "ABC"
chdir "C:\ABC\"
end sub

This code has worked well in the past, but now it will not create the
subdirectory as requested and thus fails to go to the new directory.
I made sure the worksheet was unlocked (not protected) just in case.
I am able to run macros so the security setting is not an issue.

I have run into this before and resolved it, but I don't remember
what I did to fix it.

Any suggestions? I am using Windows XP and Office XP.



8 Answers Found

Answer #1    Answered By: Angel Harris     Answered On: Nov 30

You might want to check  your use of forward slash and back slash. In
your code, all slashes should be back slash.

Real pity that the web is the other way around. I grew up with DOS, and
still sometimes get confused using my browser.

Answer #2    Answered By: Cheri Garcia     Answered On: Nov 30

you might need to put ChDir "\" after the ChDrive line

Answer #3    Answered By: Julian Long     Answered On: Nov 30

I have the "\" correctly in the program  (ChDrive "C:\"), I made  an error
when I entered the code  here. The code has worked fine  in the past  and
nothing that I know of has changed.

Is there a better way to create  a subdirectory than using this code? Can I
create a subdirectory and one under it at the same time, such as using the
command MkDir "ABC\DEF"? I think it should work, but I am not sure and
cannot test it since MkDir does not seem to work at the moment.

Answer #4    Answered By: Omar Walker     Answered On: Nov 30

Did you put

ChDir "\"

in a line following your

ChDrive "C:\" ?

If you did not the active directory  for C is not changed by the ChDrive
statement, that only changes the drive. Because of that the new dir is being
created in wherever in the C drve tree the active directory was. Then you
change to a place other than where you have created it.

An alternative would be to make your

MkDir "ABC"

MkDir "C:\ABC"

Answer #5    Answered By: Bonnie Hughes     Answered On: Nov 30

Just doing a quick Google of ChDrive. Seems to confirm my own impression
that you don't put a colon or backslash in this call. E.g.

ChDrive "C"

But ChDir, etc, will have disk and path.

Answer #6    Answered By: Percy Morgan     Answered On: Nov 30

Adding the ChDir "\" fixed the problem. The previous method
seemed to work before without the added step so I am not sure what has
happened. I will just have to remember to put that extra step in place
whenever I use that type of test. I have a program  where I use that type of
test on a regular basis.

I have never used Google to check  on VBA functions, do you just enter
"ChDir" or whatever function you are looking for into the Google question
area to get information? If not, what do you do? I normally use a book to
try and look up the function and sometimes it is difficult to find. VBA
Help is usually very difficult to use and not usually very helpful either.

Answer #7    Answered By: Aaron Kennedy     Answered On: Nov 30

IIRC I just did a Google search for chdrive, because I don't think the
term's used in other programming languages. But, yes, if you are looking
for chdir, you'd certainly need to cut it back with vba  or excel  or
similar in the search too.

Google is not a language reference, it's just that you can be pretty certain
that people will already have posted information to the web that answers
your questions.

Answer #8    Answered By: Ana Silva     Answered On: Nov 30

I put the ChDir directly into Google and got selections that definitely
helped me. I also found a number of other commands listed that helped out
as well. I will definitely try Google to see if I can find information on
commands that I don't use frequently and need some assistance.

I realize that Google is not a language reference, but there certainly was
information on some of the things I had questions about regarding VBA
commands. I also looked at their coding for making and IF statement that
could use more than seven nestings. Based on their coding, I think that a
Case statement would probably work better for what they did since that is
certainly what the coding resembled. As has been said many times, there is
more than one way to do things. Different ways are not necessarily wrong
either. Some are more efficient than others however.

Didn't find what you were looking for? Find more on Problem with Excel VBA Creating Subdirectory Or get search suggestion and latest updates.