QueryTable Null Import Quirk / Problem
 

Hi all,

I am using a QueryTable to import data from multiple CSVs (I have used /
tried many other procedures ie OpenText - but the CSVs vary in format
unpredictably - and other import routines resulted in text OR date parsing
with problems).
This has been working well until I noticed an oddity (at least with my
code): if the specified CSV is empty, the QT procedure runs without
exception. However, successive calls to the routine do NOT import any data -
it is as if the QT routine is 'stuck'. If I skip processing the empty file,
all successive CSVs import OK.
I suspect that the QT isn't being deleted / released - or maybe something
else...

I have tried adding code to delete all QTs from the import sheet to no avail.

'copyto' is a worksheet, 'FName' is CSV full file path (string)

With copyto.QueryTables.Add(Connection:="TEXT;" & FName,
Destination:=copyto.Range("A1"))
.AdjustColumnWidth = True
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.Refresh BackgroundQuery:=False
.Delete
End With

I could use FileLen to check CSV size and skip empty files if there is no
'fault' in the code etc... I'd rather not read the CSVs in line by line (or
as a string that I split on vbCrlf) etc...

Guidance greatly appreciated as ever.

Mike