' Below 2 lines will replace Column B data from "Prefer not to say" to blank and fixup date field on Colum H to yyyy-mm-dd before AD updates

' Run GET_AD_Ext_Attrib2.vbs to get extensionAttribute2 data and keep a copy for backup before below
' Run below powershell to clear the extensionAttribute2
' Run it once before we start in Prod
' Get-ADUser -Filter * -SearchBase "OU=Users,OU=VicTrack,DC=victrackad,DC=victrack,DC=com,DC=au" | ForEach-Object { $_ ; Set-ADUser $_ -Clear extensionAttribute2 }
' Get-ADUser -Filter 'Name -like "ab*"' | ForEach-Object { $_ ; Set-ADUser $_ -Clear extensionAttribute2 }
' Get-ADUser -Identity kcoelho
' Get-ADUser -Identity kcoelho | ForEach-Object { $_ ; Set-ADUser $_ -Clear extensionAttribute2 }


'On Error Resume Next  
   Const xlCSV = 6

   Start_Time = Timer    

   Set objExcel = CreateObject("Excel.Application")
   Set objWorkbook = objExcel.Workbooks.Open("C:\Autotask\AD\Tech1_to_AD\SQL_Export\input.csv")
   objExcel.DisplayAlerts = FALSE
   objExcel.Visible = FALSE
   Set objWorksheet = objWorkbook.Worksheets(1)
   Set objRange = objExcel.Range("B1").EntireColumn

' Below 2 lines will replace Column B data from "Prefer not to say" to blank

   lastRow = objWorksheet.Range("B" & objWorksheet.Rows.Count).End(-4162).row
   objWorksheet.Range("B2:B"&lastRow).Replace "Prefer not to say", ""

' Below lines will make colum H date field to yyyy-mm-dd

'     lastRow = objWorksheet.Range("H" & objWorksheet.Rows.Count).End(-4162).row
'     With objWorksheet.Range("H2:H"&lastRow)
'      .Columns.Offset(0, 1).Insert
'      .Offset(0, 1).FormulaR1C1 = "=IF(LEN(RC[-1])>0,TEXT(RC[-1],""yyyy-mm-dd""),"""")"
'      .Offset(0, 1).Copy
'      .PasteSpecial -4163
'      .Columns.Offset(0, 1).Delete
'     End With


   End_Time = Timer
   Total_Time = FormatNumber(End_Time - Start_Time, 2)     

   if  Err.Number <> 0  then 
 '       MsgBox("REPLACE : " & Date() & vbcrlf & Err.Number & vbcrlf & Err.Description)   
     else    
 '       MsgBox("REPLACE : OK! " & vbcrlf & Date() & vbcrlf & "Total Time : " & Total_Time )         
   end if

   objWorkbook.save         'save before quitting excel
   objExcel.quit