Copy from windows clipboard in VBA

[Wild Bill]

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.

 

[Chip Pearson]

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)

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.

 

[Bob Flanagan]

Bill, if you want to create a new Notepad file, then you can do direct file
writes, which will write the text to the file, and create it. You can then
also have VBA open that file if you need it opened.

Dim iFreeFile As Integer
' get a free file number
iFreeFile = FreeFile

'open file for processing
Open "C:\Info.Txt" For Output As iFreeFile

'store active cell contents in a variable
outPutLine = ActiveCell.Value

'write data stored in variable to file
Print #iFreeFile , outPutLine

'close the file
Close #iFreeFile

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Wild Bill]

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.

He shoots - he scores! Awesome! Thanks to both you and Bob.