On Saturday, 9 June 2018 18:44:04 UTC+10, GS wrote:
Does your query table include a headers row?


Thanks for your follow ups.

This example is trimmed down to the barest of the bare essentials - the
destination worksheet is bare (no headers) and the file is pure text (not
even CSV), so .Add is just plonking the file contents straight into $A.

The cut-and-paste-ready VBA fragment is:

csvFike = "c:\temp\x.txt'
Set qrytable = ActiveSheet.QueryTables.ADD(Connection:="TEXT;" + csvFile,
Destination:=Cells(1,1))

With qrytable
.Refresh BackgroundQuery:=False
nValues = .ResultRange.Rows.Count
End With

While I certainly see what you're sayin', that's not what I'm seein' - if the
file contains only a string _without_ a CR/LF, .Count nonetheless returns 2:
it's as though it's silently assumed the trailing CR/LF anyway.

Yes I suppose bypassing .Add in favor of direct VBA I/O is the way to go but
it's a simple interface that works as expected save for this pestiferous
corner case. As it is I've since used the following Truly Excruciating
Workaround (quite acceptable for the application):

If (nValues = 2) Then
(WorksheetFunction.CountA(Rows(startrow + 1)) = 0) Then nValues = 1
End If

(Moreover, .Add also throws error 7 "Out of memory" if the file consists of
two or less (not just zero) characters with no trailing CR/LF.)

Ok! IMO, choosing an approach that doesn't work consistently and coorectly all
the time just isn't a viable option regardless of its simplicity!

Using VBA file I/O doesn't throw errors unless the file doesn't exist (using a
file dialog obviates that); AND you'll always get the correct record count (and
row count if no headers in the file).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion