locked
Microsoft Excel 2010 VBA Formatting Macro assistance! Pasting help. RRS feed

  • Question

  • Sub Favorite()

    '~~> Data calling/paste area.. This is the part where there is an issue Dim DataObj As MsForms.DataObject Set DataObj = New MsForms.DataObject '<~~ Amended as per jp's suggestion '~~> Get data from the clipboard. DataObj.GetFromClipboard '~~> Get clipboard contents myString = DataObj.GetText(1) ActiveSheet.Range("A1") = myString

    '<~~ Everything below this has been working with manual input of data Range("A1").Select Selection.Replace What:="~?", Replacement:=";" Selection.Replace What:="0", Replacement:="" Selection.Replace What:="1", Replacement:="" Selection.Replace What:="2", Replacement:="" Selection.Replace What:="3", Replacement:="" Selection.Replace What:="4", Replacement:="" Selection.Replace What:="5", Replacement:="" Selection.Replace What:="6", Replacement:="" Selection.Replace What:="7", Replacement:="" Selection.Replace What:="8", Replacement:="" Selection.Replace What:="9", Replacement:="" Selection.Replace What:="~<", Replacement:="" Selection.Replace What:="~!", Replacement:="" Selection.Replace What:="~(", Replacement:="" Selection.Replace What:="~)", Replacement:="" Selection.Replace What:="~-", Replacement:=" " Selection.Replace What:="~:", Replacement:=" " Selection.Replace What:=Chr(10), Replacement:="" Selection.Replace What:=Chr(10), Replacement:="" Selection.Replace What:="Artist", Replacement:="" Selection.Replace What:="Character", Replacement:="" Selection.Replace What:="General", Replacement:="" Selection.Replace What:="Species", Replacement:="" Selection.Replace What:="Copyright", Replacement:="" ActiveCell = Application.WorksheetFunction.Trim(ActiveCell) ActiveCell = Application.WorksheetFunction.Proper(ActiveCell) ActiveSheet.Range("B8") = ActiveCell + "; Favorite Photo" Range("B8").Select Selection.Copy Range("N4").Select ActiveCell = ActiveCell + 1 ActiveSheet.Range("N4") = ActiveCell End Sub

    This macro is supposed to automatically paste the content from my clipboard into cell A1 which then activates the process of formatting it to my specified parameters.. However every time I try to make the program automatically paste it, there seems to be an issue with the end result. It places " around the content and it also seems to lack the new line removal.. Which makes the entire thing worthless.

    If anyone needs extra information feel free to post a response.

    Thanks, Saugrim

    • Edited by Saugrim Tuesday, September 10, 2013 1:01 AM
    • Moved by Cindy Meister MVP Tuesday, September 10, 2013 10:39 AM Excel-specific
    Monday, September 9, 2013 11:42 PM

Answers

  • Oh Ok :)

    Add this to your code

    Selection.Replace What:=Chr(13), Replacement:=""

    Rather here is your code which is based on my above suggestion.

    Notice how have I avoided using .Select/.Activesheet/Selection :)

    Sub marine()
        Dim DataObj As MsForms.DataObject
        Dim ws As Worksheet
        
        '~~> Change as applicable
        Set ws = ThisWorkbook.Sheets("Sheet1")
        
        Set DataObj = New MsForms.DataObject
    
        '~~> Get data from the clipboard.
        DataObj.GetFromClipboard
    
        '~~> Get clipboard contents
        myString = DataObj.GetText(1)
        
        With ws.Range("A1")
            .Value = myString
            .Replace What:="~?", Replacement:=";"
            .Replace What:="0", Replacement:=""
            .Replace What:="1", Replacement:=""
            .Replace What:="2", Replacement:=""
            .Replace What:="3", Replacement:=""
            .Replace What:="4", Replacement:=""
            .Replace What:="5", Replacement:=""
            .Replace What:="6", Replacement:=""
            .Replace What:="7", Replacement:=""
            .Replace What:="8", Replacement:=""
            .Replace What:="9", Replacement:=""
            .Replace What:="~<", Replacement:=""
            .Replace What:="~!", Replacement:=""
            .Replace What:="~(", Replacement:=""
            .Replace What:="~)", Replacement:=""
            .Replace What:="~-", Replacement:=" "
            .Replace What:="~:", Replacement:=" "
            .Replace What:=Chr(10), Replacement:=""
            .Replace What:="Artist", Replacement:=""
            .Replace What:="Character", Replacement:=""
            .Replace What:="General", Replacement:=""
            .Replace What:="Species", Replacement:=""
            .Replace What:="Copyright", Replacement:=""
            .Replace What:=Chr(13), Replacement:=""
         End With
    End Sub


    Sid


    • Edited by Siddharth Rout Wednesday, September 11, 2013 2:58 AM
    • Marked as answer by Saugrim Wednesday, September 11, 2013 3:04 AM
    Wednesday, September 11, 2013 2:57 AM

All replies

  • The initial part of the code looks familiar (From http://stackoverflow.com I guess?) ;)

    1. Please do not use Selection/Activecell etc. Please see this link on the cons of this.

    2. Also you don't need to use "Application.WorksheetFunction.Trim". VBA has it's own Trim() function

    3. "+" is used for arithmetic calculations. Use "&" for concatenating strings in VBA

    4. Now to your main problem. What is the result that you are expecting and what exactly are you getting? Perhaps you can attach a screenshot?


    Sid


    Tuesday, September 10, 2013 12:30 PM
  • Below is what the default format is, and..

        Artist
        ? DUH DUH 106
        Species
        ? DUH DUH 10205
        ? DUH DUH 5073
        General
        ? DUH DUH 21993
        ? DUH DUH 30696
        ? DUH DUH 357
        ? DUH DUH 26138

    This is what it should look like.

    ;Duh Duh ; Duh Duh ; Duh Duh ; Duh Duh ; Duh Duh ; Duh Duh ; Duh Duh;

    That is what it looks like from the program without the automatic pasting, and this..

    Is what it looks like when using the automatic pasting feature.

     ; Duh Duh   ; Duh Duh  ; Duh Duh   ; Duh Duh  ; Duh Duh  ; Duh Duh  ; Duh Duh;

    Looks exactly the same, but it isn't because when it is entered in the Windows photo tagging feature it has new lines and what not.. Really really strange.

    THIS SHOWS THE PROBLEM A BIT EASIER
    http://i1184.photobucket.com/albums/z321/coaster1000/PROBLEM_zps1a55caaa.png

    • Edited by Saugrim Wednesday, September 11, 2013 1:51 AM
    Wednesday, September 11, 2013 1:19 AM
  • Can you show me the link from where exactly are you copying?

    Sid

    Wednesday, September 11, 2013 2:39 AM
  • http://e926.net/post/show/367473/2007-bear-beard-belt-brown_hair-christopher_steven

    From the left hand side.

    Wednesday, September 11, 2013 2:45 AM
  • Oh Ok :)

    Add this to your code

    Selection.Replace What:=Chr(13), Replacement:=""

    Rather here is your code which is based on my above suggestion.

    Notice how have I avoided using .Select/.Activesheet/Selection :)

    Sub marine()
        Dim DataObj As MsForms.DataObject
        Dim ws As Worksheet
        
        '~~> Change as applicable
        Set ws = ThisWorkbook.Sheets("Sheet1")
        
        Set DataObj = New MsForms.DataObject
    
        '~~> Get data from the clipboard.
        DataObj.GetFromClipboard
    
        '~~> Get clipboard contents
        myString = DataObj.GetText(1)
        
        With ws.Range("A1")
            .Value = myString
            .Replace What:="~?", Replacement:=";"
            .Replace What:="0", Replacement:=""
            .Replace What:="1", Replacement:=""
            .Replace What:="2", Replacement:=""
            .Replace What:="3", Replacement:=""
            .Replace What:="4", Replacement:=""
            .Replace What:="5", Replacement:=""
            .Replace What:="6", Replacement:=""
            .Replace What:="7", Replacement:=""
            .Replace What:="8", Replacement:=""
            .Replace What:="9", Replacement:=""
            .Replace What:="~<", Replacement:=""
            .Replace What:="~!", Replacement:=""
            .Replace What:="~(", Replacement:=""
            .Replace What:="~)", Replacement:=""
            .Replace What:="~-", Replacement:=" "
            .Replace What:="~:", Replacement:=" "
            .Replace What:=Chr(10), Replacement:=""
            .Replace What:="Artist", Replacement:=""
            .Replace What:="Character", Replacement:=""
            .Replace What:="General", Replacement:=""
            .Replace What:="Species", Replacement:=""
            .Replace What:="Copyright", Replacement:=""
            .Replace What:=Chr(13), Replacement:=""
         End With
    End Sub


    Sid


    • Edited by Siddharth Rout Wednesday, September 11, 2013 2:58 AM
    • Marked as answer by Saugrim Wednesday, September 11, 2013 3:04 AM
    Wednesday, September 11, 2013 2:57 AM
  • Thank you so much, this has been such a hassle. ^^
    Wednesday, September 11, 2013 3:05 AM