Forum Discussion

ChrisC365's avatar
ChrisC365
Brass Contributor
Sep 24, 2020

Script to copy MS Excel files from one location to another and keep a log

Hi 

 

Not sure if PowerShell is design for this, I was thinking back to the days when you would create a BAT file.  

I am wanting to automate the process of copying 2 MS Excel files; each has a separate defined name, the source name never changes but the files are used constantly so someone may have these files open.

 

1. Script to copy the two files regardless if they are open at source.

2. Save each file to a new location(could be two separate location for each) with a new filename using the following naming convention "dd-mm-yyyy - hhmmss - <Existingfilename>" ...etc 

3. Also, create a simple log or txt to record each file being transferred, log the new filename, outcome; fail or saved, date, time and file size when it was transferred.  Save the txt file to a local folder, so over time I will build up a record when files are transferred.

4. Is it possible to have a window or progress indicator which has information for the user to see how the operation is progressing.

5. A fail safe, if the transfer does not work alert the user and stop the process, plus if feasible log the failed outcome as per step 3.

 

Chris

 

  •  

    Hi ChrisC365,

     

    Could you try this?

     

    # Input Parameters
    $Date = Get-Date -Format "dd-MM-yyyy-dddd HH-mm"
    $SrcFile1 = "filename1.xlsx"
    $SrcFile2 = "filename2.xlsx"
    $Srcfolder = "C:\Source\"
    $Dstfolder = "C:\Destination\"
    $Logs = "C:\Logs\"
    
    
    # Copy Files From Source to Destination
    
        Try
        {
        Write-Host "Copying the $SrcFile1" -ForegroundColor Yellow 
        Copy-Item -Path "$SrcFolder\$SrcFile1" -Recurse -Destination "$DstFolder\$Date - $SrcFile1" -ErrorAction SilentlyContinue
        Write-Host "Completed" -ForegroundColor Green
        }
        
        Catch
        {
        Write-Host "$SrcFile1 is not copied. Log File is in $Logs" -ForegroundColor Red
        Add-Content "$Logs\Log - $Date.txt" -Value "$SrcFile1 not copied because $($Error[0])"
        } 
    
        Try
        {
        Write-Host "Copying the $SrcFile2" -ForegroundColor Yellow 
        Copy-Item -Path "$SrcFolder\$SrcFile2" -Recurse -Destination "$DstFolder\$Date - $SrcFile2" -ErrorAction SilentlyContinue
        Write-Host "Completed" -ForegroundColor Green
        }
        
        Catch
        {
        Write-Host "$SrcFile2 is not copied. Log File is in $Logs" -ForegroundColor Red
        Add-Content "$Logs\Log - $Date.txt" -Value "$SrcFile2 not copied because $($Error[0])"
        } 

     

  • farismalaeb's avatar
    farismalaeb
    Steel Contributor

    ChrisC365 

    With powershell, the Sky is the limit, but what you provide is not enough.

    firrst of all, how can the script identify the file name if its automatically generated, is it in a fixed folder.

    • ChrisC365's avatar
      ChrisC365
      Brass Contributor

      farismalaeb 

       

      The files are not automatically generated, apology for any confusion, what I mean is I want to automate the process rather than me manually copying these files across, so for example : 

       

      existing file name is "newProjects.xlxs", I want to copy this to a new location and rename the copy as  - "24-09-2020 - 100015 - newProjects.xlsx"

       

      existing file name is "oldProjects.xlsx", I want to copy this to a new location and rename the copy as  - "24-09-2020 - 100015 - oldProjects.xlsx"

       

      Then next day or week, I repeat the process again as above except the name changes - 

      "01-10-2020 - 130014 - newProjects.xlsx"

      "01-10-2020 - 130014 - oldProjects.xlsx"

       

       

      Chris

      • farismalaeb's avatar
        farismalaeb
        Steel Contributor

        ChrisC365 

        $sourceFile="C:\MySource\lsasetup.log"
        $DestFile="C:\MySource\"+(get-date).ToString("ddMMyyyy")+".bak"
        $FailLogLocation="C:\MySource\Logcopy.txt"
        try{
        Write-Host "Copying the file, Please wait..."
        Copy-Item $sourceFile -Destination $DestFile
        }
        Catch{
        Write-Host "Ops, Failed"
        Add-Content -Path $FailLogLocation -Value $error[0]
        }

        I try to make it as simple as possible

Resources