Run Script to remove carriage returns on certain columns

ryanjohnsond@gmail.com · #16 08-25-2014, 11:17 PM

Quote:

Originally Posted by excelledsoftware

I did the research and know how to get this to work on every workbook in a folder.

That's great news!

Quote:

Originally Posted by excelledsoftware

I need to know where else to have the carriage returns removed. since this code will process 50 workbooks no problem I want to write it once and make sure it is right.

I went back and took a look, and that spread sheet is a good representation.

My main task is to remove the carriage returns with the columns that have the numbers, and replace the carriage returns with commas: they must have commas (e.g., 1,1, 1,3, 1,4,23). The problem was I couldn't' use your current code because it couldn't find the headers with the names written in the code because they both have carriage returns in them with the word "sheet" wrapping under it. My bad: I missed that originally. So, I don't need commas in the headers. Whether you find a solution to read the header names with their carriage returns, or write code to remove them, it's up to you; the main objective is to reformat the numbers in the columns with commas.

Once I fix that to match your code, then it works fine.

excelledsoftware · #17 08-25-2014, 11:33 PM

Ok I think I have a handle on it now so is it just going to be columns C and D then? or does that differ for each workbook. It can be done either way but I will need to make sure since this will overwrite the existing file once it has "cleaned" it. So just to clarify are the carriage returns just in columns C and D or are they all over?

Thanks for your patience so far. I know you will be happy with the result.

ryanjohnsond@gmail.com · #18 08-26-2014, 10:13 AM

Quote:

Originally Posted by excelledsoftware

...so is it just going to be columns C and D then? or does that differ for each workbook.

Yes. Always C and D. BUT! The row the headers "Items" and "No." starts on is not always Row 9. Sometimes the row number is at 42 or 36 or 17, but only a few instances I have seen this.

Quote:

Originally Posted by excelledsoftware

So just to clarify are the carriage returns just in columns C and D or are they all over?

They are all over, but the Database Admin only needs me to remove the carriage returns on vertically spaced numbers in columns with header names, "Items" and "No." He needs them to be separated by commas: "1,2" "2,4,6".

Quote:

Originally Posted by excelledsoftware

Thanks for your patience so far. I know you will be happy with the result.

No no... the thanks are all on my side. Thanks again, excelledsoftware!

excelledsoftware · #19 08-26-2014, 02:33 PM

Ok just give me a little time. I've got some family events tonight and then 1 thing to do tomorrow but it should not be too hard to write this out.

I will write a script that will go through all workbooks in a folder (You can specify the folder.) Then it will go through columns C and D and remove all carriage returns. (The starting row may be different for each workbook) The returns will be changed into a comma. Each of these workbooks will be saved with the same book name and left in the folder.) I should be able to have it done before the end of the week.

Thanks

ryanjohnsond@gmail.com · #20 08-26-2014, 02:59 PM

Quote:

Originally Posted by excelledsoftware

Ok just give me a little time. I've got some family events tonight and then 1 thing to do tomorrow but it should not be too hard to write this out.

I will write a script that will go through all workbooks in a folder (You can specify the folder.) Then it will go through columns C and D and remove all carriage returns. (The starting row may be different for each workbook) The returns will be changed into a comma. Each of these workbooks will be saved with the same book name and left in the folder.) I should be able to have it done before the end of the week.

Thanks

That's fantastic! (I amusing that word a lot with your coding skills).

That sounds exactly right. Caveat: The only moving variable is where the header names begin: "Items" and "No." don't always start on the 9th row, it can vary. But the columns C and D are constant. Does it matter in your code what number ROW it starts on, or does it just look for the names of the columns, starts from there and goes down the column, etc.?

ryanjohnsond@gmail.com · #21 08-27-2014, 11:52 AM

I just noticed that there are some columns that are "1-3". Your current code turns those numbers into hash tags, ####. 1-3 means numbers 1 thru 3. I need to have those left as they are.

Interesting note: After I run the macro, when I click on the #### column, the Formula url shows the formatting of "1/3/2014" I don't know what's going on.

Open and run the macro (F8) "runing_code_will_change_to_hash-test2.xlsm"
Please see attached file, "problems_with_rows_4-6.xlsm"

excelledsoftware · #22 08-27-2014, 10:47 PM

OK I tested this a couple of times and it seems to work fine.

Keep in mind that this will change every single excel file in a folder so make sure to make a backup of the entire folder before running. This will replace the carriage return with a comma in the headers as well. If we need to fix that I will have to find out the exact text in those 2 headers but other than that it appears to work.

Code:

Option Explicit
Dim cwb As Workbook, ws As Worksheet

Sub RemoveAllCarriageReturns()
  'Goes through all files of a specified folder and removes all
  'carriage returns from Columns C and D and replaces them with a comma
  'Saves the workbook with the same name in the folder and moves to the next file.
  
  Dim FolderString As String, StrFile As String, Confirm As String, wb As Workbook
  
    Confirm = MsgBox("This code will replace all carriage returns and line breaks with a comma." & vbLf & _
    "This will be done to every single workbook within the folder that you specify." & vbLf & _
    "It is strongly recommended that you create the necessary backups before running this code." & vbLf & _
    "Do you wish to continue?", vbYesNo)
    
    If Confirm = vbNo Then End
    
  
    Set wb = ThisWorkbook
  
    FolderString = InputBox("Folder Location:")
    If FolderString = "" Then End
    
    
    'Set the file folder
    StrFile = Dir(FolderString & "/")
    
    Do While Len(StrFile) <> 0
      If InStr(1, StrFile, ".xls") <> 0 Or InStr(1, StrFile, ".xlsm") <> 0 Then
        Workbooks.Open Filename:=StrFile
        Set cwb = ActiveWorkbook
        Debug.Print cwb.Name
        Set ws = cwb.Worksheets(1)
        LineBreakReplace
        cwb.Save
        cwb.Close
      End If
      Set cwb = Nothing
      Set ws = Nothing
      StrFile = Dir()
    Loop
    
    MsgBox ("Complete")

End Sub




Function LineBreakReplace()
  Dim CTotalRows As Long, DTotalRows As Long, TotalRows As Long, x As Byte
  Dim CheckRow As Long, CheckString As String, BadData As Variant
  
    'Find the Last Row
    CTotalRows = ws.Range("C50000").End(xlUp).Row
    DTotalRows = ws.Range("D50000").End(xlUp).Row
    If CTotalRows > DTotalRows Then
      TotalRows = CTotalRows
    Else
      TotalRows = DTotalRows
    End If

    On Error GoTo 0 'Reset error handling and enter line breaks in the array
    BadData = Array(vbCr, vbLf, vbCrLf, Chr(10), Chr(13))
    
    'Clean Column C
    For CheckRow = 1 To TotalRows
      CheckString = ws.Range("C" & CheckRow).Value
      For x = 0 To 4
        If InStr(1, CheckString, BadData(x)) Then
          ws.Range("C" & CheckRow).Value = Replace(CheckString, BadData(x), ",")
        End If
      Next x
    Next CheckRow
    
    'Clean Column D
    For CheckRow = 1 To TotalRows
      CheckString = ws.Range("D" & CheckRow).Value
      For x = 0 To 4
        If InStr(1, CheckString, BadData(x)) Then
          ws.Range("D" & CheckRow).Value = _
          WorksheetFunction.Substitute(CheckString, BadData(x), ",")
        End If
      Next x
    Next CheckRow
End Function

Copy and paste the entire code and then run the sub. The function will run automatically when it is called.

Let me know how it works I will be happy to fix anything if there are issues.

Thanks for this opportunity.

excelledsoftware · #23 08-27-2014, 11:55 PM

While it is on my mind I just though of 2 things you should do when you run this script the first time.
number1 open a new blank workbook and put this code into a module.
number2 try it on a folder with a few BACKED UP excel books in it.
Check the results and if everything looks good back up the intended folder and run it.

ryanjohnsond@gmail.com · #24 08-28-2014, 11:44 AM

Quote:

Originally Posted by excelledsoftware

number1 open a new blank workbook and put this code into a module.

Should I save it as an Excel Macro-Enabled Workbook (.xlsm) file?

Quote:

Originally Posted by excelledsoftware

While it is on my mind I just though of 2 things you should do when you run this script the first time.
number1 open a new blank workbook and put this code into a module.
number2 try it on a folder with a few BACKED UP excel books in it.
Check the results and if everything looks good back up the intended folder and run it.

It worked really well on all the files in the folder, but something I noticed... Apparently, there are carriage returns in unexpected places. It leaves double commas (,,).
Here is a screenshot from the results after your code ran. It shows where the double commas occurred.

Possible Solution:
Perhaps, at the end of your code you can check for double commas “,,” and remove the extra one?
E.G: your code could check for double commas, finds something like this: "2,3,,8" remove the extra comma, and the result would be "2,3,8"

ryanjohnsond@gmail.com · #25 08-28-2014, 01:15 PM

Quote:

Originally Posted by excelledsoftware

While it is on my mind I just though of 2 things you should do when you run this script the first time.
number1 open a new blank workbook and put this code into a module.
number2 try it on a folder with a few BACKED UP excel books in it.
Check the results and if everything looks good back up the intended folder and run it.

I think I did it correctly. I saved it as a Macro.xlsm, and ran it. For some reason the second folder, Column D didn't get properly changed. Weird.

excelledsoftware · #26 08-28-2014, 06:50 PM

You shouldnt even have to save the workbook that runs the code since that is all the extra workbook is for.
As for the double commas deal yes that can easily be added (I may be able to have it done later tonight)
As for the column D not getting fixed that is strange. You mentioned a 2nd folder? are you running the code for just one folder? it will only change documents ending in .xls or .xlsm that may be the problem because I believe .xlsx is a valid extension. I will change that. are there any other file types it may have skipped.

I will try to get the corrected code to you soon.

ryanjohnsond@gmail.com · #27 08-28-2014, 11:06 PM

Quote:

Originally Posted by excelledsoftware

You shouldnt even have to save the workbook that runs the code since that is all the extra workbook is for.
As for the double commas deal yes that can easily be added (I may be able to have it done later tonight)
As for the column D not getting fixed that is strange. You mentioned a 2nd folder? are you running the code for just one folder? it will only change documents ending in .xls or .xlsm that may be the problem because I believe .xlsx is a valid extension. I will change that. are there any other file types it may have skipped.

I will try to get the corrected code to you soon.

>> .xlsx is a valid extension. I will change that. >>
I am receiving all my documents first as PDF, and the I am converting them to Excel. Is there a more suitable format you prefer that I should export as? I am currently using Excel 2007.

>> You shouldn't even have to save the workbook that runs the code since that is all the extra workbook is for. >>
Interesting. just open an excel file and insert a module, paste the code and run it? I can't seem to run F8 without saving it as a macro book.

>> As for the column D not getting fixed that is strange. You mentioned a 2nd folder? are you running the code for just one folder? >>
I have multiple folders under one directory. Each directory is a category, and in each folder is a set of PDFs, that i convert to Excel and then run your code.
I am running the code in one folder at a time. then I moved to the next folder and run your code in that folder... and the process continues till I've gone through all Excel files in every folder under a main directory.

excelledsoftware · #28 08-28-2014, 11:35 PM

You can keep the same format I just need to include that extension in the code. IF F8 is not working to tap through ensure that you are tapping F8 in the sub and not in the function. I would bet that once I finish that file extension issue it should fix the inconsistencies.

excelledsoftware · #29 08-30-2014, 08:17 PM

Ok here we go. I figured there may have been some issue with the setting of the active workbook so I removed that and set it as the actual workbook intended. This looks for any file name with .xl in it so you run this on a csv or xml it will still need to be changed or you can just remove that if statement all together to have it run through EVERYTHING in the specified folder. This code will remove all double commas as well.

Code:

Option Explicit
Dim cwb As Workbook, ws As Worksheet

Sub RemoveAllCarriageReturns()
  'Goes through all files of a specified folder and removes all
  'carriage returns from Columns C and D and replaces them with a comma
  'Saves the workbook with the same name in the folder and moves to the next file.
  
  Dim FolderString As String, StrFile As String, Confirm As String, wb As Workbook
  
    Confirm = MsgBox("This code will replace all carriage returns and line breaks with a comma." & vbLf & _
    "This will be done to every single workbook within the folder that you specify." & vbLf & _
    "It is strongly recommended that you create the necessary backups before running this code." & vbLf & _
    "Do you wish to continue?", vbYesNo)
    
    If Confirm = vbNo Then End
    
  
    Set wb = ThisWorkbook
  
    FolderString = InputBox("Folder Location:")
    If FolderString = "" Then End
    
    
    'Set the file folder
    StrFile = Dir(FolderString & "/")
    
    Do While Len(StrFile) <> 0
      If InStr(1, StrFile, ".xl") <> 0 Then
        Set cwb = Workbooks.Open(FolderString & "/" & StrFile)
        Debug.Print cwb.Name
        Set ws = cwb.Worksheets(1)
        LineBreakReplace
        cwb.Save
        cwb.Close
      End If
      Set cwb = Nothing
      Set ws = Nothing
      StrFile = Dir()
    Loop
    
    MsgBox ("Complete")

End Sub




Function LineBreakReplace()
  Dim CTotalRows As Long, DTotalRows As Long, TotalRows As Long, x As Byte
  Dim CheckRow As Long, CheckString As String, BadData As Variant
  Dim DblCommaGone As Boolean
  
    'Find the Last Row
    CTotalRows = ws.Range("C50000").End(xlUp).Row
    DTotalRows = ws.Range("D50000").End(xlUp).Row
    If CTotalRows > DTotalRows Then
      TotalRows = CTotalRows
    Else
      TotalRows = DTotalRows
    End If

    On Error GoTo 0 'Reset error handling and enter line breaks in the array
    BadData = Array(vbCr, vbLf, vbCrLf, Chr(10), Chr(13))
    
    'Clean Column C
    For CheckRow = 1 To TotalRows
      CheckString = ws.Range("C" & CheckRow).Value
      For x = 0 To 4
        If InStr(1, CheckString, BadData(x)) Then
          ws.Range("C" & CheckRow).Value = Replace(CheckString, BadData(x), ",")
        End If
      Next x
    'Now clear the commas
      Do Until DblCommaGone = True
        CheckString = ws.Range("C" & CheckRow).Value
        If InStr(1, CheckString, ",,") = 0 Then
          DblCommaGone = True
        Else
          ws.Range("C" & CheckRow).Value = WorksheetFunction.Substitute(CheckString, ",,", ",")
        End If
      Loop
      DblCommaGone = False
    Next CheckRow
    
    'Clean Column D
    For CheckRow = 1 To TotalRows
      CheckString = ws.Range("D" & CheckRow).Value
      For x = 0 To 4
        If InStr(1, CheckString, BadData(x)) Then
          ws.Range("D" & CheckRow).Value = _
          WorksheetFunction.Substitute(CheckString, BadData(x), ",")
        End If
      Next x
    'Now clear the commas
      Do Until DblCommaGone = True
        CheckString = ws.Range("D" & CheckRow).Value
        If InStr(1, CheckString, ",,") = 0 Then
          DblCommaGone = True
        Else
          ws.Range("D" & CheckRow).Value = WorksheetFunction.Substitute(CheckString, ",,", ",")
        End If
      Loop
      DblCommaGone = False
    Next CheckRow
End Function

Let me know how it works Thanks

ryanjohnsond@gmail.com · #30 09-02-2014, 11:43 AM

Quote:

Originally Posted by excelledsoftware

Ok here we go. ...Let me know how it works Thanks

OK. What a weekend! Tore out my water heater, AC and some old pipes. Glad it was Labor Day.

I am going to try your new code now. Your improvements sound great! Thank you very much.

Update* The third column (Stkz.Items) isn't getting processed.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
stop carriage return or enter key in a table	Alaska1	Word	1	01-14-2013 08:48 AM
Coding into a macro a carriage return	sinbad	Word VBA	6	02-27-2012 03:51 AM
Paragraph (carriage) return font size	revrossreddick	Word	2	12-28-2011 01:33 PM
Carriage Return Help	UCHelp	Word	1	04-04-2010 10:11 PM
Table of contents, remove spacing and put in 2 columns - need help urgently!	nam085	Word	1	03-04-2010 08:00 AM