Excel – How to import data in Excel from the clipboard as if it was in a text file

If you record a macro (Tools - Macro - Record New Macro) while you open the file, you will get the parameters you need to use the OpenText method. Here's an example

Workbooks.OpenText Filename:= _
    "C:\Documents and Settings\dick\My Documents\actsynclog.txt", Origin:=437, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(8, _
    1), Array(31, 1), Array(41, 1), Array(57, 1), Array(77, 1), Array(80, 1), Array(82, 1), _
    Array(84, 1), Array(86, 1)), TrailingMinusNumbers:=True

The FieldInfo argument is the one you care about. It's an array of 2d arrays. The first element is the column and the second is the data type. This example splits the text file in the following columns: 0, 8, 31, 41, 57, 77, 80, 82, 84, and 86.

With that you, you can write a macro to open the text file. See the GetOpenFileName method of the Application object for how to select which file to open.

This is a Windows setting that will be used by all programs that refer to it (Excel in this case).

1. Click the Start button, and then click Control Panel.
2. Open the Regional and Language Options dialog box.
3. Do one of the following: In Windows Vista/7, click the Formats tab, and then click Customize this format. In Windows XP, click the Regional Options tab, and then click Customize.
4. Type a new separator in the List separator box.
5. Click OK twice.