convert excel to pipe delimited text file

Please change it.', vbCritical, 'Invalid Entry' MsgBox 'The range you entered is invalid. MsgBox 'You must enter a delimiter', vbCritical, 'Invalid Entry' MsgBox 'You must select a range', vbCritical, 'Invalid Entry'ĮlseIf. MsgBox 'You must select a worksheet', vbCritical, 'Invalid Entry' MsgBox 'You must select a workbook', vbCritical, 'Invalid Entry' ThePath = Application.GetSaveAsFilename(.tbCreateFile, 'Text Files (*.txt), *.txt',, _ The following code Is In the code module For UserForm1:įor Each ws In Workbooks(.cbWorkbook.Value).Worksheets If Item = 'L' Or Item = 'l' Then Item = UBound(X) + 1 LineStr = LineStr & IIf(CountC (UBound(X) + 1)) ThenĮlseIf Not IsNumeric(Item) And Item 'L' And Item 'l' Then

LineStr = LineStr & rng.Cells(CountR, CountC)

LineStr = LineStr & Qual & rng.Cells(CountR, CountC) & Qual If Not IsNumeric(rng.Cells(CountR, CountC)) And Not IsDate(rng.Cells(CountR, CountC)) Then ' loop through range to build text file records Set ts = fso.CreateTextFile(TheFile, True) Set fso = CreateObject('Scripting.FileSystemObject') The following code Is In regular module 'Module1':ĭelim = IIf(.obCharacter. Set cbb = (Type:=msoControlButton, Temporary:=True) Set cb = Application.CommandBars('Worksheet Menu Bar') ' add button under Tools to the Excel menu to execute the add-in

Private Sub Workbook_BeforeClose(Cancel As Boolean)Īpplication.CommandBars('Worksheet Menu Bar').Controls('&Tools').Controls('Custom Delimited File') _ The following code Is In the ThisWorkbook module: This add-in allows you to write just a portion of a worksheet to the text file, if needed, and it also allows you to specify the delimiter, the text qualifier (if any), and whether to include a 'leading' and/or 'trailing' instance of the delimiter on the record. However, sometimes we only need to save a portion of a worksheet, or we need to use a different delimiter than the comma (a semicolon perhaps, or maybe the pipe character). Typically, when we need to use Excel to create a delimited text file (for example, for uploading into another app), we set up the worksheet and then save it as a CSV file. This Excel add-in allows you to turn any range on an Excel worksheet into a delimited text file, using any delimiter string (tab, comma, any other single character or combination of characters) you like.

Convert excel to pipe delimited text file