Copy files into a current user profile folder using VBA

[borman2005]

Can someone help me with VBA code to have an Excel macro to copy files into a folder within a "current user" folder? Basically, when current user would click on a macro button, it would copy files into a specified folder within his\her userprofile.

 

[combo]

Depending on the path you need:

' default path where excel saves documents
MsgBox Application.DefaultFilePath
' requires reference to 'shell32.dll'
' returns 'My documents' path
Dim shApp As Shell32.Shell
Set shApp = New Shell32.Shell
MsgBox shApp.NameSpace(ssfPERSONAL).Self.Path

combo

 

[borman2005]

Thanks for the fast reply!
I really appreciate your help!

So, if for example, I'm logged in as "JDoe", and I want the macro to copy "C:\test\test.doc" file to "C:\Documents and Settings\JDoe\Test", how would I use it in that code?

Borman2005

 

[combo]

Complete destination path string. Use FileCopy (see help file for syntax).

combo

 

[borman2005]

Thank you so much!

I'm familiar with FileCopy, but am not sure how to specify a variable in a destination path, so it would work for the currient user. I know that %userprofile% wouldn't work here. Any suggestions?

Thanks again,
Borman2005

 

[PHV]

I know that %userprofile% wouldn't work here
Why ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[borman2005]

I've searched the use of %userprofile% variable with VBA, and apparently it's not supported.

 

[PHV]

Have a look at the Environ function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886