Next, specify the type of the data of the text with the Type property. You can modify the name according to your need. “ Student Information” is the text file name in our case. Dim FilePath As StringĪfter that, specify the active folder as the path for the text file. These two lines go through all the values in the rows and the columns of the dataset. iRow = (What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Next, declare the necessary variables for the macro. VBA Code Explanation Sub ExcelToPipeDelimitedText()įirst, provide a name for the sub-procedure of the macro. It carries the exact contents from the Excel sheet where the values from each cell are separated with pipe delimiters in the text file.
You can also just click on the small Run icon in the sub-menu bar to run the macro.Īfter the successful code execution, look at the image below to find out the result.Īs a result, there is a newly created text file named “ Student Information” (the name was provided in the code) in the active folder.
Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. TextApp = Shell("C:\WINDOWS\notepad.exe " & FileName, 1) ICol = (What:="*", SearchOrder:=xlB圜olumns, SearchDirection:=xlPrevious).ColumnįileName = FilePath & "Student Information.txt" 
IRow = (What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Then, copy the following code and paste it into the code window. Next, in the pop-up code window, from the menu bar, click Insert -> Module. In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor. This section will show you how to transfer the contents from an Excel spreadsheet to a pipe delimited text file and auto-save the file in the active folder with the VBA macro. Macro to Transfer Contents from Excel Sheet to a Pipe Delimited Text File in the Active Folder With the following dataset as the example of this article, we will show you how to convert contents from an Excel sheet to a pipe delimited text file in 3 different methods with VBA macro.ġ. The ASCII code of the pipe symbol (“|”) is 124.ģ Macro Implementations to Convert Excel Sheet to Pipe Delimited Text File When a text file holds information that is organized in a structural way separated by the pipe symbol is called a pipe delimited text file. A delimiter is a character that specifies the boundary between independent strings.
0 kommentar(er)
