Theo van Velthuijsen wrote ...

I have to read the whole file because the database which I'm connecting
to has a rocky odbc driver (hyperfile / windev). When I use the
filtering before I read the data I get error after error unfortunatly.
When I read the whole file, then I'm able to filter etc

Theo van Velthuijsen wrote ...

I have to read the whole file because the database which I'm connecting
to has a rocky odbc driver (hyperfile / windev). When I use the
filtering before I read the data I get error after error unfortunatly.
When I read the whole file, then I'm able to filter etc

Are you saying that e.g.

SELECT Col1 FROM MyTable WHERE Col2=5

causes errors but e.g.

SELECT Col1 FROM MyTable

works fine? If so, you do have problems.

I assume you are using MS Query (Data, Import External Data). If you
used ADO in VBA code, you may do some filtering there e.g.

Dim oConn As Object
Dim oRs As Object
Dim strSql As String

Set oConn = CreateObject("ADODB.Connection")

With oConn
.CursorLocation = 3 ' adUseClient
.ConnectionString = CONN_STRING
.Open
End With

strSql = "SELECT Col1" & _
" FROM MyTable"

Set oRs = oConn.Execute(strSql)
oRs.Filter = "Col2=5"
Sheet1.Range("A2").CopyFromRecordset oRs

Is it possible to read the data from that file and convert it to let say
a txt file without reading the whole file into a worksheet?

Your idea to use an intermediate data source is a good one but that
would also take some VBA coding. Rather than a text file, personally
I'd use a Jet database...

Another problem with that odbc driver is that it only works with
Excel

....ah, I see. If you have MS Access, you may be able to link the
tables and successfully query the linked tables. Definitely worth a
try. You don't need MS Access UI to do this if you can write the
equivalent VBA, but it's a lot easier with MS Access especially if you
don't know whether it will work!

So, how's your VBA abilities?