Unicorn
04-02-2003, 06:45 AM
I have a number of VBA macros in Excel that open various .TXT files and import them into Excel, where they are manipulated in some fashion and later saved in .XLS format. Here's an example of one of the open statements:
Workbooks.OpenText FileName:="C:\My Documents\dml_data.txt", Origin:= _
xlWindows, StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
1), Array(9, 1), Array(30, 1), Array(43, 1), Array(56, 1), Array(60, 1), Array(64, 1), Array _
(69, 1), Array(95, 1), Array(171, 1), Array(177, 1), Array(228, 1), Array(254, 1), Array( _
267, 1), Array(298, 1), Array(311, 1), Array(321, 1), Array(352, 1), Array(365, 1), Array( _
375, 1), Array(381, 1), Array(457, 1), Array(470, 1), Array(483, 1), Array(490, 1), Array( _
501, 1), Array(512, 1), Array(523, 1))
Some of these .TXT files are data spooled out from running SQL code from an SQL Plus prompt. Others are files e-mailed to me from an automated process in Oracle.
To make things simple, assume I can do nothing about making changes to the .TXT files I am getting...it's out of my hands.
Now, for some reason many of these files have some "garbage" characters on the first few bytes of the output. Maybe it's only one byte, like a garbled carriage return (long story there). Again, it doesn't matter what it is, because I cannot get it changed on the output.
When I run the above VBA code to import the data, even though I'm using the StartRow: command to skip that line of data, Excel returns an error that says "This file is not in a recognizable format".
The error's dialogue box has 3 choices: OK, Cancel, and Help. If I choose "OK" manually, everything continues as normal. No repercussions. As you might expect, hitting "Cancel" means the macro stops and I get the End/Debug dialogue box on top of my VBA code.
I want to A) get rid of the error message, but B) not universally get rid of all error messages, because plenty of different things can still go wrong and I need that other stuff to jump out at me.
Any ideas?
Workbooks.OpenText FileName:="C:\My Documents\dml_data.txt", Origin:= _
xlWindows, StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
1), Array(9, 1), Array(30, 1), Array(43, 1), Array(56, 1), Array(60, 1), Array(64, 1), Array _
(69, 1), Array(95, 1), Array(171, 1), Array(177, 1), Array(228, 1), Array(254, 1), Array( _
267, 1), Array(298, 1), Array(311, 1), Array(321, 1), Array(352, 1), Array(365, 1), Array( _
375, 1), Array(381, 1), Array(457, 1), Array(470, 1), Array(483, 1), Array(490, 1), Array( _
501, 1), Array(512, 1), Array(523, 1))
Some of these .TXT files are data spooled out from running SQL code from an SQL Plus prompt. Others are files e-mailed to me from an automated process in Oracle.
To make things simple, assume I can do nothing about making changes to the .TXT files I am getting...it's out of my hands.
Now, for some reason many of these files have some "garbage" characters on the first few bytes of the output. Maybe it's only one byte, like a garbled carriage return (long story there). Again, it doesn't matter what it is, because I cannot get it changed on the output.
When I run the above VBA code to import the data, even though I'm using the StartRow: command to skip that line of data, Excel returns an error that says "This file is not in a recognizable format".
The error's dialogue box has 3 choices: OK, Cancel, and Help. If I choose "OK" manually, everything continues as normal. No repercussions. As you might expect, hitting "Cancel" means the macro stops and I get the End/Debug dialogue box on top of my VBA code.
I want to A) get rid of the error message, but B) not universally get rid of all error messages, because plenty of different things can still go wrong and I need that other stuff to jump out at me.
Any ideas?