CODEWITHSUNDEEP
csvvbscript
Antonio Mailtraq
Antonio Mailtraq

Reputation: 1407

Find and replace characters in the column of CVS file in vbscript

I need find and replace on my CSV file the "; " characters in the "P" column using vbscript.

enter image description here

The old string :

non-compliant isolated T-key; isolated compass with broken insulation

The new string after CSV replace :

non-compliant isolated T-key||| isolated compass with broken insulation

I have tried this code without success.

How to do resolve this ?

My code below.

   On Error Resume Next  
   Const xlCSV = 6

   Start_Time = Timer    

   Set objExcel = CreateObject("Excel.Application")
   Set objWorkbook = objExcel.Workbooks.Open("RAS.csv")
   objExcel.DisplayAlerts = FALSE
   objExcel.Visible = TRUE
   Set objWorksheet = objWorkbook.Worksheets("RAS")   
   objWorkbook.Worksheets("RAS").Range("P:P").Replace "; ", "||| "
   objExcel.Quit  

   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

Upvotes: 0

Views: 1066

Answers (1)

Gurmanjot Singh
Gurmanjot Singh

Reputation: 10360

You are not getting any error(s) because at the beginning of the code, you have instructed your system to ignore the errors by placing On Error Resume Next statement. I commented that out and started getting the following errors in sequence:

  • It could not find the file RAS.csv, so to avoid that, I entered the fully qualified file path instead of just the file's name
  • After that I found the last row's index and replaced the contents in the Range P1 to the last row in the Column P

Also, as someone mentioned in the comments, you were not saving your workbook before quitting excel.

Code:

'On Error Resume Next  
   Const xlCSV = 6

   Start_Time = Timer    

   Set objExcel = CreateObject("Excel.Application")
   Set objWorkbook = objExcel.Workbooks.Open("E:\Work\Codes\SO\RAS.csv")    'Entered full path here
   objExcel.DisplayAlerts = FALSE
   objExcel.Visible = TRUE
   Set objWorksheet = objWorkbook.Worksheets("RAS")   
   lastRow =  objWorksheet.Range("P"&objWorksheet.Rows.Count).End(-4162).row
   objWorksheet.Range("P1:P"&lastRow).Replace "; ", "||| "


   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

End Result:

enter image description here

Upvotes: 1

Related Questions