Link to home
Avatar of Technical Information
Technical Information

asked on

Help with scripting in Excel

Hi, I need some help writing a script in excel,

I have 300 users, and with 300 computer names, I'm looking to copy data from there computers onto a shared folder on the server.

for example

user john.doe \\(computername\users$\username to be copied \\server\foldershare\username

Is there a way I could write something to lookup the values and input them without me having to write each value individually?
Avatar of Daniel Pineault
Daniel Pineault

What's the idea here, get each user to run your Excel file and it will copy the data?

You can easily retrieve the active user's username, refer to https://www.devhut.net/2018/04/12/vba-recognize-user-get-username/ and then user that to create your 2 directory string and use https://www.devhut.net/2010/11/14/ms-access-vba-copy-a-folder/ to copy the folder.

The following is untested Aircode
Sub MirrorFolder()
    Dim oFSO                  As Object
    Dim sUsername             As String
    Dim sSource               As String
    Dim sDestination          As String

    sUsername = CreateObject("WScript.Network").UserName
    sSource = "\\(computername\users$\" & sUsername
    sDestination = "\\server\foldershare\" & sUsername

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    oFSO.CopyFolder sSource, sDestination, True
    Set oFSO = Nothing
End Sub

Open in new window

Avatar of Technical Information

ASKER

I have some software that will copy the data for from users laptops to a share on the server. it will take me days to create profiles for all users. The software allows csv imports of data to create the profiles. I want to simplify the process. I'm a newby to this so not sure what the above is
> ...write something to lookup the values and input them without me having to write each value individually?

Please clarify.
Where can it generate a listing from?  Can you query Active Directory or something?  Get your users to run the code I provided earlier?  Please elaborate.
I have a list of all users, computers and paths. no I will be running the script.
Post an example of that list.
ASKER CERTIFIED SOLUTION
Avatar of Daniel Pineault
Daniel Pineault

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial