View Full Version : Solved: Text File Import - Xl Fixed Width
rajkumar
09-08-2008, 02:24 AM
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
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
09-09-2008, 01:31 AM
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:
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
09-09-2008, 06:38 AM
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:
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.