Access a text file from clipboard in Excel with VBA

Question

I try to create a macro for excel which reads the content of a text file, creates a new workbook of it and than uses a specific section inside the created workbook. To be able to get easy access to needed columns, I worked with Workbook.OpenText. But instead of inserting a fix path to my file, i would like to get the path of the file I copied to the clipboard. My Problem is, I don't know how to get access to the clipboard of windows. I found a tutorial on working with the clipboard here, but i can't get access to the expected library. I also searched also on my computer for a file called FM20.DLL like explained in this forum but couldn't find anything. Here is my code:

Workbooks.OpenText Filename:="C:\path\to.txt", Origin _
        :=-535, StartRow:=16, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1 _
        ), Array(14, 1), Array(58, 1), Array(68, 1)), TrailingMinusNumbers:=True

Does this help? http://stackoverflow.com/questions/9022245/get-text-from-clipboard-using-gettext-avoid-error-on-empty-clipboard Let me know if you still want help? — Siddharth Rout, Jul 26 '12 at 14:00
I still have a problem, accessing the file from the clipboard with OpenText. Inside the Clipboard there is no path but OpenText just works with a path. And I don't know another method, how to open a textfile in the sameway, so that the content will be formated like in my code snippet — Alex Cio, Jul 30 '12 at 07:59

score 0 · Answer 1 · answered Jul 26 '12 at 07:50

0

Assuming you do not need any user form at all

STEP 1

STEP 2

STEP 3

Now add the code

Sub ShowClipboardData()
    'In the VBA editor, go to Tools, References, and set a reference to the "Microsoft Forms 2.0 Object Library"
    Dim MyDataObject As DataObject
    Set MyDataObject = New DataObject
    MyDataObject.GetFromClipBoard
    MyDataObject.GetText
    MsgBox CStr(MyDataObject.GetText)
End Sub

answered Jul 26 '12 at 07:50

Cylian

10,970
4
42
55

Thanks for your explanation. I created the UserForm but can't delete it like in the second pic. Maybe you just can explain to me, way this has to be done. Furthermore, I have the german Version of the VBA editor. So don't know exactly how to set the Reference. Is it the big list that starts with "Global"? Because I can't find the library in this list. – Alex Cio Jul 26 '12 at 08:02
You could also manually add reference file going to ``"X:\WINDOWS\system32\FM20.DLL"``, ``X`` is the drive where windows installed. – Cylian Jul 26 '12 at 08:08
I just solved the problem, inserting the library. I haven't stopped my script, so it wasn't possible to create a reference.... You script works, but its only for plain text that will be copied from anywhere. This doesn't work with a hole File. Is it just possible to get the path too? – Alex Cio Jul 26 '12 at 09:11

score 0 · Accepted Answer · answered Aug 03 '12 at 08:57

I just solved the problem on a different way. I don't use the OpenText method anymore. I used following code to insert from clipboard into my worksheet:

Range("A1").Select
ActiveSheet.Paste

This isn't the answer of my question, but looks much easier to me as the answer before. It also worked, but i had to import library manually and write a lot more code. The way i have to access the code is now another. I jump from line to line, get save the line in a variable, delete the whitespace from the string

stringLine = Replace(Range("A" & row).Value, " ", "")

and create an array with the several values by using

anArray = Split(stringLine, "|")

Anyway, thank you for help ;)

Access a text file from clipboard in Excel with VBA

2 Answers2