How to Use the OpenText Method in Excel

VBA is used in Excel to create macros which automate tasks that would normally be done manually. Conducting these tasks manually can be time consuming and cumbersome. Many times users need to copy and paste data into Excel from text files. Simply copying and pasting the data from a text file into Excel will not result in properly formatted data. The OpenText method is used to load a text file and parse it. A spreadsheet is created with the parsed data. This method eliminates the need for the user to manually import the text file and go through the "Text Import Wizard".

Instructions

1. Open a new workbook. Press Alt and F11 on your keyboard to open the VBA editor. Click on the File menu and select "New". Double click on Sheet(Sheet1) under Microsoft Excel Objects in the Project-VBAProject window pane.

2. Type Sub, followed by the macro name in the first line, such as Sub Open(). You can give the macro any name you would like.

3. Add the OpenText method, specifying the following information:

   1. Directory the file is located in.
   2. Row number you would like begin importing the data in. The file origin is 437: OEM United States by default if you are in the United States.
   3. Choose whether you want to parse the file in a delimited versus fixed width. With delimited, you must specify what the delimiter is to parse the file, such as tab, semicolon, comma, space, or a customized delimiter. Set the delimiter you would like to use to TRUE, and the rest of the delimiters to FALSE.

   For example, if we want to parse a text file located on John Smith's desktop, separated by tabs, we would use the following code:

   Workbooks.OpenText Filename:= _
   "C:\Documents and Settings\Jsmith\Desktop\info.txt", Origin:=437, StartRow _
   :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
   ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
   , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
   TrailingMinusNumbers:=True

4. End the macro by typing "End Sub". Save the macro by clicking on the Save button in the toolbar. Close the Visual Basic window.

5. Run the macro by clicking on the View tab in the toolbar. Click on Macros, and select the macro. Click on the "Run" button.

References

• MSDN Office Developer Center