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.)