Make directory at Network Login name

[VishalkumarT]

Dear Friends

I need your help regarding how to create a directory using Network Login Name.

I know we can make directory MyDirectory at C:\ using one-line code
[VBA]MkDir "C:\MyDirectory\"[/VBA]

But suppose if I want to share my macro-code (in which I need to create directory while running it first time) amongst different users on a network, for example, users A,B and C log in into the network using their login names UsernameA, UsernameB and UsernameC respectively; and they have their network drive with their login name folder H:\Netowrk Username\ , then how can I make MyDirectory in this folder.

I mean on the network drive,
H:\Network Username\MyDirectory

where Network Username would be UsernameA for A, UsernameB for B and UsernameC for C.

I hope you would understand what I mean to write here.

Thank you once again.
Best Regards
Vishalkumar

[Simon Lloyd]

Vishalkumar, i think its something like this:
[VBA]MkDir "H:\"& Environ("UserName") & \MyDirectory"[/VBA]

[Bob Phillips]

Simon,

You would need to do it in stages

[vba]

MkDir "H:\" & Environ("UserName")
MkDir "H:\" & Environ("UserName") & "\MyDirectory"
[/vba]

[Simon Lloyd]

Why the two stages Bob? (nice to hear from you!)

[Bob Phillips]

Becuase it will fail if the parent directory does not already exist.

Done properly, it should have error handling in case either does/does not exist.

[VishalkumarT]

Hi Simon
Hello xld

I tried to use both the alternatives shown here by you both.

But Compilation error was coming, so I changed it to

[VBA]MkDir "H:\" & Environ("UserName") & "\MyDirectory"[/VBA]

by putting extra " after second & sign.
And then I tried to run it, but it shows error messages, path couldn't find.
I am wondering how does it recognise different UserNames with this code

Vishalkumar

[Bob Phillips]

See my responses explaining this.

[VishalkumarT]

But with this code

[VBA]MkDir "H:\" & Environ("UserName")
MkDir "H:\" & Environ("UserName") & "\MyDirectory"[/VBA]

this error comes:

[VBA]
Run time error 75
Path/file access error[/VBA]

[VishalkumarT]

Hello

By writing the code in this protection it works:

[VBA]On Error Resume Next
MkDir "H:\" & Environ("UserName")
MkDir "H:\" & Environ("UserName") & "\MyDirectory"
On Error GoTo 0[/VBA]

Will it affect the other part of my code, I mean, wherever I will use MyDirectory to store some result or generated files in the code ?

Thank you so much...

[Bob Phillips]

Nope.

[VishalkumarT]

Thank you once again to both of you

[OdiN]

Hello

By writing the code in this protection it works:

[vba]On Error Resume Next
MkDir "H:\" & Environ("UserName")
MkDir "H:\" & Environ("UserName") & "\MyDirectory"
On Error GoTo 0[/vba]
Will it affect the other part of my code, I mean, wherever I will use MyDirectory to store some result or generated files in the code ?

Thank you so much...

I would probably do something along these lines:

[vba]
If Dir("H:\" & Environ("UserName"), vbDirectory) = "" Then

MkDir "H:\" & Environ("UserName")
MkDir "H:\" & Environ("UserName") & "\MyDirectory"

ElseIf Dir("H:\" & Environ("UserName") & "\MyDirectory", vbDirectory) = "" Then

MkDir "H:\" & Environ("UserName") & "\MyDirectory"

Else

MsgBox "Directories Exist", vbOKOnly

End If
[/vba]
But that's just me. Depends on what you're trying to do.

[VishalkumarT]

Nice things to learn.

Thanks everybody.

[VishalkumarT]

Dear Friends

Now I am finding a strange problem (may be for me), I used the trick to find the destination folder on network, using the replies of this post, but i am finind an error, if I have windows/excel of different languages. Some of the network users have English version and some have German version of Excel or office.

I am using this code with a user defined button to activate it, this is "Custom Button" in English "Benutzerdefinierte Schaltfl?che" in German in the code.

I mean,

[VBA]Set mycontrol = mybar.Controls.Item("Custom Button") ' for English version[/VBA]

or

[VBA]Set mycontrol = mybar.Controls.Item("Benutzerdefinierte Schaltfl?che") ' for German version[/VBA]

May I know how I can educate the macro code, that if English version is there then do this and if German version is there then do that ? Though I want to use the same code for If and for Else If part, but just want to perform the action for both of these languages.

Many thanks for your precious time and help.

Vishalkumar

[Bob Phillips]

Look at the value of

[vba]

application.International(xlcountrysetting)
[/vba]

in the German version and test that. UK is 44, I guess US is 01, and Germany should be 49?

[VishalkumarT]

Yes it is 49.

Thanks.

Let me check it.

[VishalkumarT]

It is working

Thank you.

Best Regards
Vishalkumar