If you already have the text in the Windows clipboard, you can use code like
the following:
Sub AAA()
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard
Range("A1").Value = DataObj.GetText
End Sub
You'll need to set a reference to the MSForms library. In the VBA editor, go
to the Tools menu and choose References. There, find the entry for
"Microsoft Forms 2.0 Object Library" and check it. See
www.cpearson.com/Excel/Clipboard.htm for more info.
If the text is not already in the clipboard, you can simply do a Save in
Notepad (no need to close Notepad) and then read the text file with code
like the following:
Sub BBB()
Dim FNum As String
Dim FName As Variant
Dim LineOfText As String
Dim S As String
FName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If FName = False Then
' user cancelled Open dialog
Exit Sub
End If
FNum = FreeFile
Open FName For Input Access Read As #FNum
Do Until EOF(FNum)
Line Input #FNum, LineOfText
' use the first S = S line if you do NOT want to
' ignore blank lines
S = S & vbCrLf & LineOfText
' use the If/End If statement if you DO want to ignore
' blank lines
If Len(LineOfText) > 0 Then
S = S & vbCrLf & LineOfText
End If
Loop
Close #FNum
Range("A1").Value = S
End Sub
Unfortunately, there is no way to automate NotePad, so you'll have to
manually do the File Save in NotePad to get the file text back to the disk
file.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)
"Wild Bill" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Posted to microsoft.public.excel, microsoft.public.excel.programming
> Copy from windows clipboard in VBA
>
> Thank you each for even your time reading this whether or not you reply.
> It was hard to shorten this. I want to paste [Notepad] text to a single
> cell. I've resigned myself to Ctrl-A, Ctrl-C in Notepad - but want to
> automate the rest. I'll be doing this many times.
>
> (A)I've hit dead ends with .paste, .pastespecial as Excel seems to
> disregard the "outside" clipboard.
>
> I've experimented with two angles seeking whole VBA automation or
> perhaps even a single keystroke+VBA.
> (B)Straight Ctrl-V is complicated by embedded linefeeds. I wipe out
> cells beneath the active cell. I can work around that with a dedicated
> cell at the bottom of the sheet - or even a scratch worksheet. What
> would be the command to copy the scratch cell? Something like
> activecell.formulaR1C1=range("ScratchCell").value
> or
> activecell.value=range("ScratchCell").value
> or some .Copy? Remember those deadly linefeeds!
>
> (C)I can manually hit F2,Ctrl-V,Enter. This gets it done in one shot but
> I can't imagine emulating it in VBA. Can VBA even at least cut down the
> number of strokes?
>
> So how should I paste multiline clipboard text from VBA - or at least
> cut down the manual part?
>
> P.S. I'm reticent to use SendKeys for the usual reasons - but I'll put
> that thought out there too. From where I'm at now, it's a temptation.
Similar Threads
