Query Table for Text / CSV Files

If you update some Excel frequently, you can keep it as shared and then ask your fellow colleagues to check if often (refresh)

One of the good option is to have them as CSV file and use query table to update it regularly

Sub TXT_QueryTable()

Dim ConnString As String

Dim qt As QueryTable

ConnString = "TEXT;C:\Temp.txt"


Set qt = Worksheets(1).QueryTables.Add(Connection:=ConnString, _
Destination:=Range("B1"))

qt.Refresh

End Sub

The Refresh method causes Microsoft Excel to connect to the query table’s data source, execute the SQL query, and return data to the query table destination range. Until this method is called, the query table doesn’t communicate with the data source.