Announcement

Collapse
No announcement yet.

Insert text from clipboard to cell on currently selected row

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Insert text from clipboard to cell on currently selected row

    I have a worksheet using Excel 2002 that will automatically insert a text string from the clipboard that works but the problem is that I need it to insert the text on the line that I have highlighted and it will only do that on the first line and then it wants to re-write the line above it instead of inserting the text on the line where I want it (may be the second or third line down with a blank line in-between). Here is the Activate & Insert code so maybe someone can tell me what is wrong?
    Thanks, Gene

    Here is the type of string that will be on the clipboard -- A17; Bill Smith; Mary Smith; Phoenix; AZ; Bigfoot; 21FW; 2003; 8 ; 15; 19; [email protected] ; Joey


    Code:
    'Summary: Returns text from the clipboard as a string.
    
    'Does the clipboard contain format?
     Private Declare Function IsClipboardFormatAvailable _
       Lib "User32.dll" _
         (ByVal wFormat As Integer) As Long
    
    'Open the clipboard
     Private Declare Function OpenClipboard _
       Lib "User32.dll" _
         (ByVal hwnd As Long) As Long
    
    'Get a pointer to the formatted data
     Private Declare Function GetClipboardData _
       Lib "User32.dll" _
         (ByVal wFormat As Integer) As Long
    
    'Close the clipboard
     Private Declare Function CloseClipboard _
       Lib "User32.dll" () As Long
    
    'Finds first double Chr$(0) in a string
     Private Declare Function lstrlen _
       Lib "kernel32.dll" _
         Alias "lstrlenA" _
           (ByVal lpString As String) As Long
           
    'Returns a pointer to the clipboard data in memory
     Private Declare Function GlobalSize _
       Lib "kernel32.dll" _
         (ByVal hMem As Long) As Long
         
    'Prevent the clipboard data from being overwritten
     Private Declare Function GlobalLock _
       Lib "kernel32.dll" _
         (ByVal hMem As Long) As Long
         
    'Free the memory used by the clipboard data
     Private Declare Function GlobalUnlock _
       Lib "kernel32" _
        (ByVal hMem As Long) As Long
        
    'Copy clipboard data from protected memory to a string buffer
     Private Declare Sub MoveMemory _
       Lib "kernel32" Alias "RtlMoveMemory" _
        (ByVal strDest As Any, _
         ByVal lpSource As Any, _
         ByVal Length As Long)
        
        
    Function GetClipboardText() As String
    
      Dim DataSize As Long
      Dim hClip As Long
      Dim hData As Long
      Dim pData As Long
      Dim Ret As Long
      Dim strText As String
      
        Const CF_TEXT As Long = 1&
        
          Ret = OpenClipboard(0&)
            If Ret = 0 Then
               MsgBox "Clipboard is in use."
               Exit Function
            End If
          
            Ret = IsClipboardFormatAvailable(CF_TEXT)
              If Ret Then
                 hData = GetClipboardData(CF_TEXT)
                   If hData Then
                      DataSize = GlobalSize(hData)
                      strText = Space$(DataSize)
                      pData = GlobalLock(hData)
                        Call MoveMemory(strText, pData, DataSize)
                        strText = Left$(strText, lstrlen(strText))
                      Call GlobalUnlock(hData)
                   End If
              Else
                 MsgBox "There is No Text on the Clipboard."
              End If
            
        Ret = CloseClipboard
        GetClipboardText = strText
       
    End Function
    Code:
    Sub InsertText()
      
      Dim Rng As Range
      Dim RngEnd As Range
      Dim Text As String
      
          Set Rng = Range("A2:M2")
          Set RngEnd = Cells(Rows.Count, Rng.Row).End(xlUp)
          Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Range(Rng, RngEnd).Offset(1, 0))
          
          Text = GetClipboardText
          If Text <> "" Then Rng.Rows(1).Cells.Value = Split(Text, ";")
          
    End Sub
    
    Sub ActivateInsertKey()
    
      Application.OnKey "{INSERT}", "InsertText"
      
    End Sub
    Last edited by AAE; September 3rd, 2010, 05:23. Reason: revise thread title

  • #2
    Re: Need Macro help please

    Hello genestoy,

    Welcome to Ozgrid.

    Thread titles are important for obtaining good search results and should be written with this thought in mind -- that is, the title should be "search friendly", meaning a search using YOUR title as the search terms will yield relevant results. Thread titles should concisley and accurately describe the content of your thread.

    Your title of "Need macro Help Please" does not adequately describe your thread and is of not help to others searching the forum for a solution to a similar problem.

    Click the EDIT POST button below your thread, then click Go Advanced and revise the thread title to something that describes your thread/objective.

    Take some time to read the Forum Rules so that you can avoid infractions that may impact your posting privilege. Thanks.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

    Comment


    • #3
      Re: Need Insert Text Row Macro help please

      If you want it to be on the currently selected row then why all the work with Rng and RngEnd? Would not Activecell.Row put you on the highlighted row?

      Your example code will only fill (from what I can see) rows 2 and 3 because of the integer value in the offset. If you had used RngEnd-1 instead of the 1 the code would fill the first empty row after 2.
      Last edited by rikcando; September 3rd, 2010, 05:14. Reason: oops, fixed a typo

      Comment


      • #4
        Re: Need Insert Text Row Macro help please

        Thanks for the reply but I am a novice at Excel and did not write the above code so could you explain or show me in the code what needs to be changed?
        Thanks for your help
        Gene

        Comment


        • #5
          Re: Need Insert Text Row Macro help please

          If you are looking to have the text placed into the row that you have highlighted, the following code will do that.
          Code:
          Sub InsertText()
            
            Dim Rng As Range
            Dim RngEnd As Range
            Dim Text As String
            
                Set Rng = Range("A" & ActiveCell.Row & ":M" & ActiveCell.Row)
          '      Set RngEnd = Cells(Rows.Count, Rng.Row).End(xlUp)
          '      Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Range(Rng, RngEnd).Offset(RngEnd.Row - 1, 0))
                
                Text =GetClipboardText
                If Text <> "" Then Rng.Rows(1).Cells.Value = Split(Text, ";")
                
          End Sub

          Comment


          • #6
            Re: Need Insert Text Row Macro help please

            rikcando,
            You are a life saver!!!! That works perfectly and I thank you so much!
            Gene

            Comment


            • #7
              Re: Insert text from clipboard to cell on currently selected row

              I need a little help with this again, first it has been working just fine but I now find I need one of the columns (the "I" column) to do a counta which I have already set up but the problem is that the insert above stores the number in the I column as text so the counta won't work with text. How do I make the insert store that one column as a number for the counta? Or if that is too hard to do what code would I use fof that "I" column to total the entries that have numbers in them, not a addition of all the numbers but a total of the cells with numbers in them but excluding any cells with text? Thanks again for any help!

              Comment


              • #8
                Re: Insert text from clipboard to cell on currently selected row

                UPDATE: I figured it out so no help needed, thanks!

                Comment

                Working...
                X