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?

VB ScriptMicrosoft ExcelScripting LanguagesMicrosoft Office

Last Comment

Daniel Pineault

8/22/2022 - Mon

Daniel Pineault

3/8/2020

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

Technical Information

3/8/2020

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

NVIT

3/8/2020

> ...write something to lookup the values and input them without me having to write each value individually?

Please clarify.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

Daniel Pineault

3/8/2020

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.

Technical Information

3/8/2020

ASKER

I have a list of all users, computers and paths. no I will be running the script.

NVIT

3/8/2020

Post an example of that list.

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

ASKER CERTIFIED SOLUTION

Daniel Pineault

3/9/2020

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question