The Help section in Excel's VBA told me this:

expression.Add(Connection, Destination, Sql)

Destination Required Range. The cell in the upper-left corner of the query table destination range (the range where the resulting query table will be placed). The destination range must be on the worksheet that contains the QueryTables object specified by expression

I noticed you have ActiveSheet.Add... etc, but your Destination is on
"Sheet2." You might consider changing the ActiveSheet to Sheet2. Maybe that
would fix it?

FILENAME = Application.GetOpenFilename("All Files (*.csv), *.txt")
If FILENAME < False Then
MsgBox "Open " & FILENAME
End If
With ActiveSheet.QueryTables.Add(Connection:="Text;" & FILENAME,
Destination:=Range("Sheet2!$A$1"))

Change last line to:

With Sheet2.QueryTables.Add(Connection:="Text;" & FILENAME, _
Destination:=Range("Sheet2!$A$1"))

I haven't used the QueryTables command before, and I don't really know how
to mimic the situation, unfortunately. And I understand the need to not have
a hardcoded file path like:
Data Source=G:\Research\LII\Research\LII\Administration \Clients\NYSE"
It'll always find a way to nip you in the butt down the road (like in 5
years when they change the folder tree organization).

Hope this helps, Jim
--
I appreciate any feedback.