How about this...
Record a macro when you file|open one of those .txt files--mainly to get all
that parsing information correct.
The file will open as a separate workbook. You can copy that imported data
whereever you want--at the bottom of column A in the current worksheet.
Dim wks As Worksheet
Dim newWks As Worksheet
Dim DestCell As Range
Dim myFileName As Variant
myFileName = Application.GetOpenFilename("Text files, *.txt")
If myFileName = False Then
Exit Sub 'user hit cancel
Set wks = ActiveWorkbook.Worksheets("Sheet1")
'with all your parsing info here
Set newWks = ActiveSheet 'just opened
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
> Here is my situation. I work for an electronics repair facility, a new
> company we are doing work for is sending jobs to us via FTP as .txt
> files. If you open these text files using notepad, they look like
> jumbled words. For example,
> 0000001111232Johnsmithbeverlyhillsca90210. What I need to do is tell
> Excel to import this file using this formula: the first 'xx' spaced
> are for the customer's ID number, the next 'xx' spaces are for the
> customer's first name, the next 'xx' spaces are for last name, etc.
> Here is what I tried so far:
> 1. Set column headings on Row1 (Customer First Name, Last Name,
> Address, etc) (columns A-V)
> 2. File -> Open -> sample.txt
> 3. Text import wizard ->Step 1 selected 'Fixed Width' -> Step 2 Set
> column widths -> Step 3 clicked finished
> After I clicked finished, every column (all 20 of them) were formatted
> perfectly on Row2. Now here is the problem. I made a test file and
> tried importing that onto Row3. I clicked on cell A3, went to Data ->
> Get External Data, but "Import Text File" was grayed out (unavailable).
> The only options I have are "Edit Text Import" or "Data Range
> I then tried to see if I can import the new file onto Row4. I clicked
> on cell A4, went to Data -> Get External Data, this time "Import Text
> File" was available so I clicked it, then navigated to sample2.txt.
> Then Excel sent me through the Text import wizard again!!!
> My question is, is there a way I can copy the formatting from Row2 to
> the rest of the spreadsheet? Ideally, when a new file lands in our FTP
> server, I just want to open Excel, click on the next available row,
> click 'import text file', navigate to the file, and be done.
> I should add that all the text files will be formatted the same (25
> spaces for first name, 25 for last name, 30 for city, etc.)
> Thank You,