Copy Range of Cells, Removing CR&LF and Pasting to Notepad

[Heinrichxx]

Hello
I have a sheet "Data".
Would like to copy a range "G11:H95".
The range should have removed all CR&LF and replace the CR&LF with ";".
This data is needed for a powershell script.
After removing the CR&LF I would like to paste it to notepad in one line.
How can this be accomplished.
Thank you
Heinrichxx

 

[OaklandJim]

This code does what you specified. Hopefully you can modify the "FileSpecification variable to be your full path and file name.

Sub MakeSemicolonDelimitedFile()
    
    Dim wsSource As Worksheet
    
    Dim rDataRange As Range
    
    Dim rCell As Range
    
    Dim sCellContent As String
    
    Dim sStringout As String
    
'   Path and filename for text file
    Dim sTextFileSpecification As String
    
'   File number for next "free" text file.
    Dim iTextFileNum As Long
    
'   This is worksheet where data is located.
    Set wsSource = ThisWorkbook.Worksheets("Sheet2")
    
'   This is where data to be processed is located.
    Set rDataRange = wsSource.Range("G11:H95")
    
'   This includes full path and file name of text file to save.
    sTextFileSpecification = "C:\Users\Bob\Desktop\Test.txt"
    
'   Delete the file if it already exists
    On Error Resume Next
    Kill (sTextFileSpecification)
    On Error GoTo 0
    
'   Iterate through all source data cells.
    For Each rCell In rDataRange
        
'       Remove CR with nothing
        sCellContent = Replace(rCell.Value, Chr(13), "")
        
'       Replace LF with semicolon
        sCellContent = Replace(sCellContent, Chr(10), ";")
        
'       Add the cell's content to the full output string
        sStringout = sStringout & sCellContent
        
    Next rCell
    
'   Get the next free file # from Excel
    iTextFileNum = FreeFile
    
'   Open the text file for output
    Open sTextFileSpecification For Output As iTextFileNum
    
'   Put the string into the text file.
    Print #iTextFileNum, sStringout
    
'   Close and save the text file
    Close iTextFileNum

End Sub

 

[Heinrichxx]

Hello OaklandJim
Very very nice. Thank you.

But the script runs only when in developer modus and by pressing the command "run this sub/user form".
I have changed the -- Set wsSource = ThisWorkbook.Worksheets("Sheet2") -- with the name of the sheet but without any result of the behaviour

Also there are no spaces beetween the cells and the ";" gets not inserted.

I have added some printscreens

Regards

Heinrichxx

 

[OaklandJim]

I think that we may have a language problem...

My code does what I thought that you asked for.

The range should have removed all CR&LF and replace the CR&LF with ";".

I do not understand this.

Also there are no spaces beetween the cells and the ";" gets not inserted.

Try rewriting that statement differently.

Consider using MrExcel's excellent Addin that allows you to post portions of your worksheet so we can see your data.

See here XL2BB - Excel Range to BBCode

Better yet, provide a link to the file? Use Dropbox or OneDrive.

 

[Heinrichxx]

I think that we may have a language problem...

My code does what I thought that you asked for.

I do not understand this.


Try rewriting that statement differently.

Consider using MrExcel's excellent Addin that allows you to post portions of your worksheet so we can see your data.

See here XL2BB - Excel Range to BBCode

Better yet, provide a link to the file? Use Dropbox or OneDrive.

Hello
You are right it is a language Problem.
Thought the same way. How can I adapt the script.

I did send a printscreens all the CR&LF are removed but ";" are not inserted.

For the spaces please see the printscreen.

Regards

Heinrichxx

 

[Alex Blakenburg]

This is mostly @OaklandJim's code with a couple of changes, see it this is what you meant.

Sub MakeSemicolonDelimitedFile_Jim()
   
    Dim wsSource As Worksheet
    Dim rDataRange As Range
    Dim rCell As Range
    Dim sCellContent As String
    Dim sStringout As String
    Dim lrowData As Long                                        ' XXX Added
   
'   Path and filename for text file
    Dim sTextFileSpecification As String
   
'   File number for next "free" text file.
    Dim iTextFileNum As Long
   
'   This is worksheet where data is located.
    Set wsSource = ThisWorkbook.Worksheets("Sheet2")
   
'   This is where data to be processed is located.
    lrowData = wsSource.Range("G" & Rows.Count).End(xlUp).Row   ' XXX Added
    Set rDataRange = wsSource.Range("G11:H" & lrowData)         ' XXX Modified
   
'   This includes full path and file name of text file to save.
    sTextFileSpecification = "C:\Users\Bob\Desktop\Test.txt"
   
'   Delete the file if it already exists
    On Error Resume Next
    Kill (sTextFileSpecification)
    On Error GoTo 0
   
'   Iterate through all source data cells.
    For Each rCell In rDataRange.Columns(1).Cells               ' XXX Loop modified
       
'       Add the cell's content to the full output string
        sStringout = sStringout & rCell.Value & " " & rCell.Offset(, 1).Value & ";"
       
    Next rCell
   
'   Remove trailing semi-colon
    sStringout = Left(sStringout, Len(sStringout) - 1)
   
'   Get the next free file # from Excel
    iTextFileNum = FreeFile
   
'   Open the text file for output
    Open sTextFileSpecification For Output As iTextFileNum
   
'   Put the string into the text file.
    Print #iTextFileNum, sStringout
   
'   Close and save the text file
    Close iTextFileNum

End Sub

 

[Heinrichxx]

This is mostly @OaklandJim's code with a couple of changes, see it this is what you meant.

Sub MakeSemicolonDelimitedFile_Jim()
  
    Dim wsSource As Worksheet
    Dim rDataRange As Range
    Dim rCell As Range
    Dim sCellContent As String
    Dim sStringout As String
    Dim lrowData As Long                                        ' XXX Added
  
'   Path and filename for text file
    Dim sTextFileSpecification As String
  
'   File number for next "free" text file.
    Dim iTextFileNum As Long
  
'   This is worksheet where data is located.
    Set wsSource = ThisWorkbook.Worksheets("Sheet2")
  
'   This is where data to be processed is located.
    lrowData = wsSource.Range("G" & Rows.Count).End(xlUp).Row   ' XXX Added
    Set rDataRange = wsSource.Range("G11:H" & lrowData)         ' XXX Modified
  
'   This includes full path and file name of text file to save.
    sTextFileSpecification = "C:\Users\Bob\Desktop\Test.txt"
  
'   Delete the file if it already exists
    On Error Resume Next
    Kill (sTextFileSpecification)
    On Error GoTo 0
  
'   Iterate through all source data cells.
    For Each rCell In rDataRange.Columns(1).Cells               ' XXX Loop modified
      
'       Add the cell's content to the full output string
        sStringout = sStringout & rCell.Value & " " & rCell.Offset(, 1).Value & ";"
      
    Next rCell
  
'   Remove trailing semi-colon
    sStringout = Left(sStringout, Len(sStringout) - 1)
  
'   Get the next free file # from Excel
    iTextFileNum = FreeFile
  
'   Open the text file for output
    Open sTextFileSpecification For Output As iTextFileNum
  
'   Put the string into the text file.
    Print #iTextFileNum, sStringout
  
'   Close and save the text file
    Close iTextFileNum

End Sub

Hello
Fantastic. It did work. Would it be possible to change " Set rDataRange = wsSource.Range("G11:H" & lrowData) ' XXX Modified" to Set rDataRange = wsSource.Range("G11:H95"). There will be allways 95 lines.
Regards
Heinrichxx

 

[Alex Blakenburg]

Sure, that was supposed to be a value add
If you change it back to a fixed number of rows and you end up having less rows with data, you will wind up with a whole heap of semi-colons at the end of your data.

Here is how to change it back.

' Delete this line
    Dim lrowData As Long                                        ' XXX Added
' Delete this line
    lrowData = wsSource.Range("G" & Rows.Count).End(xlUp).Row   ' XXX Added
'  Change this line back to what it was
    Set rDataRange = wsSource.Range("G11:H" & lrowData)         ' XXX Modified
    Set rDataRange = wsSource.Range("G11:H95")

 

[Heinrichxx]

Sure, that was supposed to be a value add
If you change it back to a fixed number of rows and you end up having less rows with data, you will wind up with a whole heap of semi-colons at the end of your data.

Here is how to change it back.

' Delete this line
    Dim lrowData As Long                                        ' XXX Added
' Delete this line
    lrowData = wsSource.Range("G" & Rows.Count).End(xlUp).Row   ' XXX Added
'  Change this line back to what it was
    Set rDataRange = wsSource.Range("G11:H" & lrowData)         ' XXX Modified
    Set rDataRange = wsSource.Range("G11:H95")

Hi Alex
You are right and it is great to have.
My script prepares the the csv for Teams creation of CallQueue and AutoAttendant creation.
As Microsoft often changes settings it is surely a great enhancement.
Regards
Heinrichxx

 

[Alex Blakenburg]

Glad it is working for you. @OaklandJim's did a great job given how little information was provided in the initial request.
His code was nice and clear so it was pretty ease to incorporate the additional information you provided in post #3 into his code.