Copy File Help

[mdex]

Morning all,

I'm trying to use access to copy a file from a network location to local machine.

I can copy the file no problem with absolute path names but want to use environment variables for network users.

OldName = "\\cottonwood\SomeExample.xls"
NewName = "D:\Documents and Settings\%USERNAME%\My Documents\SomeExample2.xls"
retval = 0
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(OldName, NewName, True)
Set objFSO = Nothing
MsgBox "Copy/paste complete"

For the life of me I cannot figure it out.

I realise the new name string cannot have %username% like it is but just copied the code in as an example.

Also tried this way...

Dim afterpath As String

afterpath = Environ(appdata)

OldName = "\\cottonwood\SomeExample.xls"
NewName = afterpath

Just can't get my head around the Environ or if it's even the correct way of doing it?

Any help appreciated.

 

[Golom]

NewName = "D:\Documents and Settings\" & Environ$("USERNAME") & _
          "\My Documents\SomeExample2.xls"

 

[mdex]

Cheers Golom.

Thought Environ was the way to go.

mdex