Help Importing Text Files

[MikeG]

Hello,

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
Properties".

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,
Mike

 

[Dave Peterson]

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.

Option Explicit
Sub testme01()

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
End If

Set wks = ActiveWorkbook.Worksheets("Sheet1")

Workbooks.OpenText Filename:=myFileName
'with all your parsing info here

Set newWks = ActiveSheet 'just opened

With wks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

newWks.UsedRange.Copy _
Destination:=DestCell

wks.UsedRange.Columns.AutoFit

newWks.Parent.Close savechanges:=False

End Sub