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?
VB ScriptMicrosoft ExcelScripting LanguagesMicrosoft Office

Avatar of undefined
Last Comment
Daniel Pineault

8/22/2022 - Mon
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

Technical Information

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.
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.
Daniel Pineault

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

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

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.
Daniel Pineault

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
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