We help IT Professionals succeed at work.

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

John Carney
John Carney asked
on
Medium Priority
258 Views
Last Modified: 2012-06-27
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008

Commented:
That is VBA. What is your question again?

Kevin
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

byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

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

Brad
John CarneyReliability Business Tools Analyst II

Author

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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
Macro for F2 + CTRL v
Sub SpecialPaster()
Application.SendKeys "{F2}^{v}"
End Sub

Open in new window


Brad
CERTIFIED EXPERT
Top Expert 2008

Commented:
I would add a close command to that:

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

Kevin
John

Did you try the code that I posted above?

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

Sid
CERTIFIED EXPERT
Top Expert 2008

Commented:
Hmmm, Mr. Yundt's been doing this for a very long time.
CERTIFIED EXPERT
Top Expert 2008

Commented:
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
John CarneyReliability Business Tools Analyst II

Author

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
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2008
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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

John CarneyReliability Business Tools Analyst II

Author

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

- John
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.