locked
Word & Excel Batch Old to New Format Conversion using Office 365 PS Script. This Newbie Needs Help Please! RRS feed

  • Question

  • Im a still learning PS/Scripting and was looking to see if anyone could take a shot at fixing this as I don't know what should be "$Path = ?" Variable hope this is all that is all that It needs to work properly :P . Any help for this Newbie is appreciated lol & laughs on Me ><

    Thanks

    //////////////////////

    Set-StrictMode -Version 2


    $RootPath ="Z:\OldDocs\"
    $Path = @
    $Paths +="2004"
    $Paths +="2005"
    $Paths +="2006"
    $Paths +="2007"
    $Paths +="2008"
    $Paths +="2009"
    $Paths +="2010"
    $Paths +="2011"
    $Paths +="2012"
    $Paths +="2013"
    $Paths +="2014"
    $Paths +="2015"

    $BackupRootPath = "Z:\OldDocs\BUFolder"

    $ErrorActionPreference = 'Inquire'
    $WarningPreference = 'Inquire'

    $Word = New-Object -ComObject word.application
    $Excel = New-Object -ComObject Excel.application

    $WordSaveFormat = [System.Enum]::Parse([Microsoft.Office.Interop.Word.WdSaveFormat],"WdFormatDocumentDefault")
    $WordSaveFormatMacro = [System.Enum]::Parse([Microsoft.Office.Interop.Word.WdSaveFormat],"WdFormatXMLDocumentMacroEnabled")

    $ExcelSaveFormat = [System.Enum]::Parse([Microsoft.Office.Interop.Excel.XlFileFormat],"xlWorkbookDefault")
    $ExcelSaveFormatMacros = [System.Enum]::Parse([Microsoft.Office.Interop.Excel.XlFileFormat],"xlOpenXMLWorkbookMacroEnable")

    $Outpath = ""


    function SetFileTimes([string]$oldFilePath,[string]$newFilePath){
       $oldFile = $null
        $newFile = $null
         $oldFile = Get-Item -Path $oldFilePath
          $newFile = Get-Item -Path $newFilePath
       if ($newFile -ne $null){
            if ($oldFile -ne $null){
                 $newFile.CreationTime= $oldFile.CreationTime
                   $newFile.LastAccessTime= $oldFile.LastAccessTime
                     $newFile.LastWriteTime= $oldFile.LastWriteTime
       }
      }
     }
     function BuildPath([string]$ExistingPath, [string] $Extension){
       $TempPath =""
       $tempPath = ($ExistingPath).Substring(0,($ExistingPath).LastIndexOf(".")).Replace($folderpath,$outpath)

      if($TempPath.Length -lt 255) {
            $TempPath = $TempPath + $Extension
       }else{
            Stop

          }
          Return $TempPath
     }

    function ConvertDocRTF($CurrDocPath){
         Try{
           $OpenDoc = $Word.documents.open($CurrDocPath,$false,$true) #ConfirmConversions = False, ReadOnly = True
            $OpenDoc.ActiveWindow.View = 3 #WdPrintVeiw
        $OpenDoc.Convert()
        }Catch{
            $OpenDoc = $null}
         if($OpenDoc -ne $null){
              if($OpenDoc.hasVBProject -eq $false){
                  $SaveName = BuildPath $CurrDocPath ".docx"
                  Try{
                       $OpenDoc.SaveAs([ref]"$SaveName", [ref]$WordSaveFormat)
                     }Catch{$SaveName = ""}
                       }Else{
                            $SaveName = BuildPath $CurrDocPath ".docm"
                            Try{
                                  $OpenDoc.SaveAs([ref]$WordSaveFormatMacro)
                                  }Catch{$SaveName = ""}
                  }

                  $OpenDoc.close();
                  return $SaveName
                  }Else{
                         Return ""
                         }
                         }

    function ConvertExcel($CurrBookPath){
       $OpenBook = $null
       Try{
              #$oBooks = $Excel.Workbooks

              #$oBooks.GetType().InvokeMember("Open", [System.Reflection.BindingFlags]::InvokeMethod, Nothing, oBooks,Nothing, ci)

              $OpenBook = $Excel.Workbooks.Open($CurrBookPath,$false,$true) #ConfirmConversions = False, ReadOnly = True

     }Catch{
         $OpenBook =$null
        }

        if($OpenBook -ne $null){
             $OpenBook.CheckCompatibility =$false

             if($OpenBook.HasVBProject -eq $false){
                 $SaveName = BuildPath $CurrBookPath ".xlsx"

             Try{
                 $OpenBook.SaveAs("$SaveName",$ExcelSaveFormat)
                   }Catch{$SaveName = ""}
            }Else{
                 $SaveName = BuildPath $CurrBookPath ".xlsm"
             Try{
                 $OpenBook.SaveAs("$SaveName",$ExcelSaveFormatMacros)
                 }Catch{$SaveName = ""}
               }
               $Openbook.close();
               Return $SaveName
            }
          }

    foreach ($FolderPath in $paths) {
              $FolderPath = $RootPath + $FolderPath;
              Write-Host $FolderPath
              If((Test-Path $FolderPath) -eq $true){
                  Get-ChildItem -Path ($FolderPath + "\*") -Include "*.*" | ForEach-Object -Process{
                     $NewFilePath = ""
                     $CurrFilePath = $_.FullName
                     $Outpath = $FolderPath
                     $BackUpPath = $FolderPath.Replace($RootPath,$BackupRootPath)
                If((Test-Path $BackUpPath) -eq $false){
                     New-Item -ItemType Directory -Force -Path $BackUpPath
                     }
                     switch($_.Extension.ToLower())
                     {
                      ".doc"{$NewFilePath =ConvertDocRTF ($CurrFilePath)}
                      ".rtf"{$NewFilePath =ConvertDocRTF ($CurrFilePath)}
                      ".wpd"{$NewFilePath =ConvertDocRTF ($CurrFilePath)}

                      ".xls"{$NewFilePath =ConvertExcel ($CurrFilePath)}
                      default{}
                    }
                    If($NewFilePath -ne ""){
                        SetFileTime $CurrFilePath $NewFilePath
                        Write-Host $NewFilePath
                        Move-Item $CurrFilePath $BackUpPath
                        }
                    }
                }
            }

    #ClosePrograms
    $Word.quit()
    $Excel.quit()

                     
    Wednesday, July 22, 2015 5:34 PM

Answers

  • Sorry, but this isn't a "here's a long code dump, can someone figure it out and debug it all for me" forum.

    This forum is designed to answer specific questions.

    If you want for someone to write a script for you, you can add a request here:

    https://gallery.technet.microsoft.com/scriptcenter/site/requests

    You can also browse the script repository (link at the top of this forum) to search pre-written scripts.


    -- Bill Stewart [Bill_Stewart]

    • Marked as answer by Joshi3Boy Wednesday, July 22, 2015 6:00 PM
    Wednesday, July 22, 2015 5:48 PM

All replies

  • Sorry, but this isn't a "here's a long code dump, can someone figure it out and debug it all for me" forum.

    This forum is designed to answer specific questions.

    If you want for someone to write a script for you, you can add a request here:

    https://gallery.technet.microsoft.com/scriptcenter/site/requests

    You can also browse the script repository (link at the top of this forum) to search pre-written scripts.


    -- Bill Stewart [Bill_Stewart]

    • Marked as answer by Joshi3Boy Wednesday, July 22, 2015 6:00 PM
    Wednesday, July 22, 2015 5:48 PM
  • Okay sry about this I will remove and ask for help Thank you Bill for the link

    Wednesday, July 22, 2015 5:49 PM
  • Just a note - it is about 100 lines more than is needed to convert the files.


    \_(ツ)_/

    Wednesday, July 22, 2015 6:01 PM
  • Thank you for the tip. I was also trying to retain dates with said Files Lol figures I always take the long way around. lol still a newbie needs to be told that this the wrong way tisktisk'  :P

    Wednesday, July 22, 2015 6:25 PM