I have created a text file using the Open Statement. How do I delete a specific line say line 3 using VBA codes? Any help is very much appreciated. Thank you.

You need to read the existing file and write out the required lines to a new file, then delete the existing file and rename the new one to the same name

If the lines that you want to omit can be dropped with SQL, you can use the ODBC text driver & ADO to select the lines you want. Then use the GetString method to write the lines to a new file. Amount to the same thing!

"AA2e72E" wrote ...

If the lines that you want to omit can be dropped with
SQL, you can use the ODBC text driver & ADO to select
the lines you want.

I've never been able to delete a row / line in a text file (nor
Excel!) using SQL, either using the ODBC text driver or the OLE DB
Provider for Jet e.g.

DELETE
FROM Patient.txt
WHERE Patient_ID = 2

returns an error. If you have been able to do this I'd be interested
to learn the details.

What I am able to do (OLEDB and ODBC) is to select the required rows
into a new text file e.g.

SELECT *
INTO MyNewFile.txt
FROM Patient.txt
WHERE Patient_ID < 2

Perhaps this is what you were suggesting? (I don't get what you mean
by 'GetString'.)

The above relies on having a key column. Of course, SQL is set based
rather than procedural and therefore has no concept of 'row number'.
There is no way to say DELETE ROW 3 in SQL (barring a kludge!)

Jamie.

--

Thanks AA2e72E for your suggestion.

"AA2e72E" wrote:

You need to read the existing file and write out the required lines to a new file, then delete the existing file and rename the new one to the same name.

If the lines that you want to omit can be dropped with SQL, you can use the ODBC text driver & ADO to select the lines you want. Then use the GetString method to write the lines to a new file. Amount to the same thing!