asked on

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

zorvek (Kevin Jones)

That is VBA. What is your question again?

Kevin

SiddharthRout

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

byundt

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

Brad

John Carney

ASKER

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

byundt

Macro for F2 + CTRL v

Sub SpecialPaster()
Application.SendKeys "{F2}^{v}"
End Sub

Open in new window

Brad

zorvek (Kevin Jones)

I would add a close command to that:

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

Kevin

SiddharthRout

John

Did you try the code that I posted above?

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

Sid

zorvek (Kevin Jones)

Hmmm, Mr. Yundt's been doing this for a very long time.

zorvek (Kevin Jones)

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 Carney

ASKER

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

SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SiddharthRout

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 Carney

ASKER

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

- John