Screen Updating = True Close #FNum End Sub '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Do The Export ' This prompts the user for the File Name and the separtor ' character and then calls the Export To Text File procedure. Value End If Whole Line = Whole Line & Cell Value & Sep Next Col Ndx Whole Line = Left(Whole Line, Len(Whole Line) - Len(Sep)) Print #FNum, Whole Line Next Row Ndx End Macro: On Error Go To 0 Application.
See Importing A Fixed Width File for code to import a fixed width text file into Excel. This procedure allows you to export data from a worksheet range to a text file. If your text file contains greater the 64K records, or you need to split the imported text across multiple worksheets, see Importing Big Text Files Into Excel. Public Sub Import Text File(FName As String, Sep As String) Dim Row Ndx As Long Dim Col Ndx As Integer Dim Temp Val As Variant Dim Whole Line As String Dim Pos As Integer Dim Next Pos As Integer Dim Save Col Ndx As Integer Application. Items in the text file are separated into separate columns on the worksheet row based on the character you specify. Column Row Ndx = Active Open FName For Input Access Read As #1 While Not EOF(1) Line Input #1, Whole Line If Right(Whole Line, 1) = 1 Temp Val = Mid(Whole Line, Pos, Next Pos - Pos) Cells(Row Ndx, Col Ndx).
Screen Updating = False 'On Error Go To End Macro: Save Col Ndx = Active Cell. For more depth see Install a Macro or User Defined Function on my Formula page.Speed and efficiency considerations can be seen in Proper, and other Text changes and in Slow Response. Generally to activate things you have changed the underlying formats. Also available on the code\page are Sep Last Name for people's names, Sep Last Word uses Str Reverse new in Excel 2000, Last Word function uses Str Reverse, resubstr function using VBA Script.Without some kind of a limitation the macro subroutines would take a very long time cycling through 16384 rows in XL95, or 65536 rows in XL97 and later. Row If i Rows Switch selected cells from "lastname, firstnames" to "firstnames lastnames" based on having a comma in position 2 or higher.The limitation can be corrected better (come back in a year). Calculation = xl Calculation Automatic Application. Screen Updating = True End Sub Lastname() can be used to rearrange cells so that lastname appears first in cells in selected range. If there is a comma in a cell, the cell will be left alone; otherwise, the cell will be recomposed with the word after the last space first followed by a comma, and the first names. Sub Lastname() 'David Mc Ritchie 1999-04-09 ' cells in range in as Lastname, firstnames '--Application. This code is shorter and more efficient than the lastname macro because it was written later. Sub First Name() 'David Mc Ritchie 2000-03-23 programming ' Application. Calculation = xl Manual Dim cell As Range Dim c Pos As Long For Each cell In Selection. If the user cancels either of these dialogs, the procedure is terminated and no export operation is carried out.