Solved: Text File Import - Xl Fixed Width

[rajkumar]

Hi,

I have a text file downloaded from Oracle which i am importing to excel
and deleting unwanted rows except column heading and saving it as xls.

My problem is the Oracle text file is of fixed width type when i import it to excel.

There is no standard delimiter. So if i record a macro, i end up screwing some characters in any of the column.

Can any one help with a code that can do this job for me.

I have attached both text and xls files for reference.

(In the unwanted chr file the highlighted row is the column heading)


Thanks in advance

Raj :help

[Ago]

is it correct that you need a macro that looks in column A for a:

1, Blank cell
2, The word Xerox
3, or --------

and if the cell contains any of those the whole line should be removed?

it wont solve all your problems but thats a start right?

[rajkumar]

Absolutely, Yes that's the start.

I need a macro to kill the entire row that has junk characters( including blank rows).

I also want to reuse the same macro for different text files in the attachment.

I have attached all my reports, could anyone help on this please:help

Raj :hi:

[Ago]

this will delete the unwanted lines on your first file.

Sub CleanUp()
Application.ScreenUpdating = False
CurrentRow = 1
While CurrentRow < Cells(Rows.Count, 1).End(xlUp).Row

If Range("A" & CurrentRow).Value = "" Or Range("A" & CurrentRow).Value Like "*---*" Or _
Range("A" & CurrentRow).Value Like "*Xerox*" Or Range("A" & CurrentRow).Value = "" Or _
Range("A" & CurrentRow).Value = "Engno" And CurrentRow > 1 Then

Rows(CurrentRow).EntireRow.Delete
CurrentRow = CurrentRow - 1

End If
CurrentRow = CurrentRow + 1
Wend
Rows(CurrentRow + 1).EntireRow.Delete
Application.ScreenUpdating = True
End Sub


i think all macros has to be costum made for the other files.
i havent looked at them very closly yet

[rajkumar]

Hi Ago,

Superb, It is working fine. i need the importing the text file also through vba.

Is it possible to make that also..

Regards
Raj :hi:

[Ago]

add this in the begining of the macro.


FileName = "<Path+Filename>" '<---edit
Row =1 ' sets the row the import should start at

If Dir(FileName) <> Empty Then
Open (FileName) For Input As #1
Do While Not EOF(1)
Input #1, FileText
Cells(Row, "A") = FileText
Row = Row + 1
Loop
Close #1
End If