Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Macro that pastes a copied string into the FORMULA of the active cell

I need something that will say in effect : ActiveCell.Formula = whatever string is currently in copy memory.

I'm copying from an external PDF file, and I find that if I select a cell and paste as usual, Excel distributes it to 2 or 3 cells, one for every line skip, whereas I need the entire string in one cell. But if I select the cell and paste into the formula bar, then I get it all in the one cell. Like this:

ActiveCell.FormulaR1C1 = "E" & Chr(10) & "1035622"

How do I do that in VBA?

Thanks,
John
0
gabrielPennyback
Asked:
gabrielPennyback
  • 5
  • 4
  • 3
  • +1
3 Solutions
 
zorvek (Kevin Jones)ConsultantCommented:
That is VBA. What is your question again?

Kevin
0
 
SiddharthRoutCommented:
Try this.

Paste this in a module and after copying from the pdf simply run the macro sample.

Sid

Code

Declare Function abOpenClipboard Lib "User32" Alias "OpenClipboard" (ByVal Hwnd As Long) As Long
Declare Function abCloseClipboard Lib "User32" Alias "CloseClipboard" () As Long
Declare Function abEmptyClipboard Lib "User32" Alias "EmptyClipboard" () As Long
Declare Function abIsClipboardFormatAvailable Lib "User32" Alias "IsClipboardFormatAvailable" (ByVal wFormat As Long) As Long
Declare Function abSetClipboardData Lib "User32" Alias "SetClipboardData" (ByVal wFormat As Long, ByVal hMem As Long) As Long
Declare Function abGetClipboardData Lib "User32" Alias "GetClipboardData" (ByVal wFormat As Long) As Long
Declare Function abGlobalAlloc Lib "Kernel32" Alias "GlobalAlloc" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Declare Function abGlobalLock Lib "Kernel32" Alias "GlobalLock" (ByVal hMem As Long) As Long
Declare Function abGlobalUnlock Lib "Kernel32" Alias "GlobalUnlock" (ByVal hMem As Long) As Boolean
Declare Function abLstrcpy Lib "Kernel32" Alias "lstrcpyA" (ByVal lpString1 As Any, ByVal lpString2 As Any) As Long
Declare Function abGlobalFree Lib "Kernel32" Alias "GlobalFree" (ByVal hMem As Long) As Long
Declare Function abGlobalSize Lib "Kernel32" Alias "GlobalSize" (ByVal hMem As Long) As Long
Const GHND = &H42
Const CF_TEXT = 1
Const APINULL = 0

Sub Sample()
    Dim strCode As String
    strCode = Clipboard2Text
    ActiveCell.Formula = strCode
End Sub

Function Clipboard2Text()
    Dim wLen As Integer
    Dim hMemory As Long
    Dim hMyMemory As Long

    Dim lpMemory As Long
    Dim lpMyMemory As Long

    Dim retval As Variant
    Dim wFreeMemory As Boolean
    Dim wClipAvail As Integer
    Dim szText As String
    Dim wSize As Long

    If abIsClipboardFormatAvailable(CF_TEXT) = APINULL Then
        Clipboard2Text = Null
        Exit Function
    End If

    If abOpenClipboard(0&) = APINULL Then
        MsgBox "Unable to open Clipboard.  Perhaps some other application is using it."
        GoTo CB2T_Free
    End If

    hMemory = abGetClipboardData(CF_TEXT)
    If hMemory = APINULL Then
        MsgBox "Unable to retrieve text from the Clipboard."
        Exit Function
    End If
    wSize = abGlobalSize(hMemory)
    szText = Space(wSize)

    wFreeMemory = True

    lpMemory = abGlobalLock(hMemory)
    If lpMemory = APINULL Then
        MsgBox "Unable to lock clipboard memory."
        GoTo CB2T_Free
    End If

    ' Copy our string into the locked memory.
    retval = abLstrcpy(szText, lpMemory)
    ' Get rid of trailing stuff.
    szText = Trim(szText)
    ' Get rid of trailing 0.
    Clipboard2Text = Left(szText, Len(szText) - 1)
    wFreeMemory = False

CB2T_Close:
    If abCloseClipboard() = APINULL Then
        MsgBox "Unable to close the Clipboard."
    End If
    If wFreeMemory Then GoTo CB2T_Free
    Exit Function

CB2T_Free:
    If abGlobalFree(hMemory) <> APINULL Then
        MsgBox "Unable to free global clipboard memory."
    End If
End Function

Open in new window

0
 
byundtCommented:
John,
What happens if you F2 and then do your Paste? Does that not do what you want?

Brad
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
gabrielPennybackAuthor Commented:
Hi Brad, how do I incorporate that into a macro. In effect that's what I'm doing when I paste into the formula bar and hit enter. The question is how do I tell Excel to do that in a macro? :-)

Thanks,
John
0
 
byundtCommented:
Macro for F2 + CTRL v
Sub SpecialPaster()
Application.SendKeys "{F2}^{v}"
End Sub

Open in new window


Brad
0
 
zorvek (Kevin Jones)ConsultantCommented:
I would add a close command to that:

Application.SendKeys "{F2}^{v}{ENTER}"

Kevin
0
 
SiddharthRoutCommented:
John

Did you try the code that I posted above?

Brad, I wouldn't suggest Sendkeys as they are highly unreliable.

Sid
0
 
zorvek (Kevin Jones)ConsultantCommented:
Hmmm, Mr. Yundt's been doing this for a very long time.
0
 
zorvek (Kevin Jones)ConsultantCommented:
And when did SendKeys become highly unreliable? It works for me when needed. Remember that unreliability is usually associated with different UI modes (such as inactive or hidden windows) because SendKeys sends the keystrokes to the active window. Beyond that it very reliable.

Kevin
0
 
gabrielPennybackAuthor Commented:
Hi Sid, I opened my PDF andtried running 'Sample' but it simply put the entire contents of the VBA module into the active cell. ?? Do I need to identify my PDF by name? Something else you can see?

Thanks,
John
0
 
byundtCommented:
Sid,
John is copying from an external pdf, so I assume he isn't doing the copy with VBA. It is the paste that he wants to automate.

I'd do it with F2 followed by CTRL + v and Enter. No macro at all.

If John insists on a macro, then I reckon Kevin's improvement to my SendKeys ought to be plenty reliable enough. It's a one-line sub the way I think he wants to use it.
Sub SpecialPaster()
Application.SendKeys "{F2}^{v}{ENTER}"        'Includes Kevin's improvement
End Sub

Open in new window

Brad
0
 
zorvek (Kevin Jones)ConsultantCommented:
Sid,

Sub PasteItWithoutSidsExtraJunk()

    Dim Clipboard As Object
    Dim ClipboardText As String
    Set Clipboard = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    Clipboard.GetFromClipboard
    ClipboardText = vbNullString
    On Error Resume Next
    ClipboardText = Clipboard.GetText
    On Error GoTo 0
    If Len(ClipboardText) > 0 Then
        ActiveCell.Value = ClipboardText
    End If

End Sub

Kevin
0
 
SiddharthRoutCommented:
John: You need to copy the text before running the code.

Kevin: That is a nice one.

Sid
0
 
SiddharthRoutCommented:
Sorry Brad. Missed your post. It definitely is a one line sub code though :) without any junk as Kevin referred to LOL

>>>>Remember that unreliability is usually associated with different UI modes (such as inactive or hidden windows) because SendKeys sends the keystrokes to the active window.
Kevin: I completely agree.

Sid

0
 
gabrielPennybackAuthor Commented:
Hi Sid, apparently I forgot to copy before running the code. Amazing, huh? Thank you all.

- John
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now