Copy text after last comma in cell - Page 5

Thanks Thanks:  0
Likes Likes:  0
Page 5 of 5 FirstFirst ... 345
Results 41 to 47 of 47

Thread: Copy text after last comma in cell

  1. #41
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,267
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy text after last comma in cell

    And may be the better is Txt = .Replace(Txt, LineSeparator) instead of Txt = .Replace(Txt, vbCrLf)
    Vladimir Zakharov
    Microsoft MVP Excel

  2. #42
    New Member
    Join Date
    Nov 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    Thank you so much for your help!

    Right now I've got this:
    I click a button and that saves my excel sheet as a csv-document. But now I can't run your "cleaning-code" (from ZVI) because the Excel-CSV- document is still open. Do you know a way how to crate a csv-document (maybe by asking for a different name than the original?) without changing the original?

    Right now I've got this, but here I need to close the original (press: do not save changes) and open it again so I can press the button with the "cleaning-code"

    Private Sub Save_Click()
    'save document as csv
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\Desktop\Folder\testdata.csv" _
    , FileFormat:=xlCSV, CreateBackup:=False

    'close original, don't save changes
    ActiveWorkbook.Open savechanges:=False
    Workbooks.Open Filename:="C:\Users\Desktop\Folder\Original.xlsm"
    End Sub

  3. #43
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,267
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy text after last comma in cell

    Quote Originally Posted by fro94 View Post
    ... I can't run your "cleaning-code" (from ZVI) because the Excel-CSV- document is still open. Do you know a way how to create a csv-document (maybe by asking for a different name than the original?) without changing the original? ...
    Try this:
    Code:
    Private Sub Save_Click()
      
      Const CsvFile = "C:\Users\Desktop\Folder\testdata.csv"
       
      Dim Wb As Workbook
      
      ' Remember active workbook
      Set Wb = ActiveWorkbook
      
      ' Turn blinking off
      Application.ScreenUpdating = True
      
      ' Copy active sheet to the temporary new workbook
      ActiveSheet.Copy
      
      ' Save temporary workbook as CSV
      With ActiveWorkbook
        .SaveAs Filename:=CsvFile, _
                FileFormat:=xlCSV, CreateBackup:=False, _
                Local:=False
        ' Close it
        .Close False
      End With
      
      ' Restore selection
      Wb.Activate
      
      ' Restore screen updating
      Application.ScreenUpdating = True
      
      ' Clean up extra commas at the end of CSV lines
      FixCsv CsvFile
      
    End Sub
    
    
    Sub FixCsv(Optional CsvFile As String)
     
      Const LineSeparator = vbCrLf  ' <-- Change to suit
     
      Dim Txt As String, PathName As String, Fso As Object, File As Object
     
      ' Get PathName of CSV file
      If Len(CsvFile) = 0 Then
        ' Choose CSV file
        With Application.FileDialog(msoFileDialogOpen)
          .InitialFileName = ThisWorkbook.Path
          .Filters.Clear
          .Filters.Add "CSV files", "*.csv"
          .AllowMultiSelect = False
          If .Show = False Then Exit Sub
          PathName = .SelectedItems(1)
        End With
      Else
        ' Check the presence of CSV file
        PathName = CsvFile
        If Dir(PathName) = "" Then
          MsgBox "File not found:" & vbLf & PathName, vbCritical, "Exit"
          Exit Sub
        End If
      End If
     
      ' Trap errors
      On Error GoTo exit_
     
      ' Read CSV file
      Set Fso = CreateObject("Scripting.FileSystemObject")
      Set File = Fso.OpenTextFile(PathName, 1)
      Txt = File.ReadAll
      File.Close
     
      ' Trim all commas at the end of lines
      If Right(Txt, Len(LineSeparator)) <> LineSeparator Then Txt = Txt & LineSeparator
      With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = ",+" & LineSeparator
        Txt = .Replace(Txt, LineSeparator)
      End With
       
      ' Write CSV
      Set File = Fso.CreateTextFile(PathName, True)
      File.Write Txt
      File.Close
     
    exit_:
     
      ' Report
      If Err Then
        MsgBox Err.Description, vbCritical, "Error #" & Err.Number
      Else
        MsgBox "File is fixed now:" & vbLf & PathName, vbInformation, "Well done!"
      End If
     
    End Sub
    Regards
    Last edited by ZVI; Nov 20th, 2017 at 12:43 PM.
    Vladimir Zakharov
    Microsoft MVP Excel

  4. #44
    New Member
    Join Date
    Nov 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    That is great!

    Is there the possibility to vary the name of the .csv-file by the content of sudden cells? Like this?

    ActiveWorkbook.SaveAs Range("Table1!B2").Value & Range("Table1!C4").Value & ".csv"


    And is it possible to save another workbook than the active one eg. "Table 2" instead of telling "ActiveWorkbook"?


    Dim Wb As Workbook

    ' Remember active workbook
    Set Wb = ActiveWorkbook

    Thanks a lot

  5. #45
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,267
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy text after last comma in cell

    Check the User Settings constant on the top of Save_Click() and try:
    Code:
    Private Sub Save_Click()
     
      ' --> User Settings, change to suit
      Const PathCell = "Table1!B2"  ' Path to CSV with "\" at the end, like "C:\Users\Desktop\Folder\"
      Const FNameCell = "Table1!C4" ' Name of CSV file, like "testdata"
      Const DataSheet = "Table2"    ' Name of the sheet with data for CSV file
      ' <-- End of User Setings
     
      Dim CsvFile As String, Wb As Workbook
     
      ' Build full path name to CSV file
      CsvFile = Range(PathCell).Value
      CsvFile = CsvFile & Range(FNameCell).Value & ".csv"
     
      ' Remember active workbook
      Set Wb = ActiveWorkbook
     
      ' Turn blinking off
      Application.ScreenUpdating = True
     
      ' Copy data sheet to the temporary new workbook
      Worksheets(DataSheet).Copy
     
      ' Save temporary workbook as CSV
      With ActiveWorkbook
        .SaveAs Filename:=CsvFile, _
                FileFormat:=xlCSV, CreateBackup:=False, _
                Local:=False
        ' Close it
        .Close False
      End With
     
      ' Restore selection
      Wb.Activate
     
      ' Restore screen updating
      Application.ScreenUpdating = True
     
      ' Clean up extra commas at the end of CSV lines
      FixCsv CsvFile
     
    End Sub
     
     
    Sub FixCsv(Optional CsvFile As String)
     
      Const LineSeparator = vbCrLf  ' <-- Change to suit
     
      Dim Txt As String, PathName As String, Fso As Object, File As Object
     
      ' Choose CSV file
      If Len(CsvFile) = 0 Then
        ' Choose CSV file
        With Application.FileDialog(msoFileDialogOpen)
          .InitialFileName = ThisWorkbook.Path
          .Filters.Clear
          .Filters.Add "CSV files", "*.csv"
          .AllowMultiSelect = False
          If .Show = False Then Exit Sub
          PathName = .SelectedItems(1)
        End With
      Else
        ' Check the presence of CSV file
        PathName = CsvFile
        If Dir(PathName) = "" Then
          MsgBox "File not found:" & vbLf & PathName, vbCritical, "Exit"
          Exit Sub
        End If
      End If
     
      ' Trap errors
      On Error GoTo exit_
     
      ' Read CSV file
      Set Fso = CreateObject("Scripting.FileSystemObject")
      Set File = Fso.OpenTextFile(PathName, 1)
      Txt = File.ReadAll
      File.Close
     
      ' Trim all commas at the end of lines
      If Right(Txt, Len(LineSeparator)) <> LineSeparator Then Txt = Txt & LineSeparator
      With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = ",+" & LineSeparator
        Txt = .Replace(Txt, LineSeparator)
      End With
      
      ' Write CSV
      Set File = Fso.CreateTextFile(PathName, True)
      File.Write Txt
      File.Close
     
    exit_:
     
      ' Report
      If Err Then
        MsgBox Err.Description, vbCritical, "Error #" & Err.Number
        Err.Clear
      Else
        MsgBox "File is fixed now:" & vbLf & PathName, vbInformation, "Well done!"
      End If
     
    End Sub
    Vladimir Zakharov
    Microsoft MVP Excel

  6. #46
    New Member
    Join Date
    Nov 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    Quote Originally Posted by ZVI View Post
    Check the User Settings constant on the top of Save_Click() and try:
    Code:
    Private Sub Save_Click()
     
      ' --> User Settings, change to suit
      Const PathCell = "Table1!B2"  ' Path to CSV with "\" at the end, like "C:\Users\Desktop\Folder\"
      Const FNameCell = "Table1!C4" ' Name of CSV file, like "testdata"
      Const DataSheet = "Table2"    ' Name of the sheet with data for CSV file
      ' <-- End of User Setings

    I get an error.... because of the "variable constants" I think. What other type of data can I use there to create the name of the csv-file?

    Especially the error accours in line "CsvFile=Range(PathCell).Value

  7. #47
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,267
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy text after last comma in cell

    Quote Originally Posted by fro94 View Post
    Especially the error accours in line "CsvFile=Range(PathCell).Value
    This means that you have no sheet named as "Table1".
    Please do check the tab name of that sheet, its real name can be "Table 1" (with space char) then use Const PathCell = "'Table 1'!B2".
    Last edited by ZVI; Nov 20th, 2017 at 07:26 PM.
    Vladimir Zakharov
    Microsoft MVP Excel

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com