Excel VBA: Set new external data connection and refresh without querying user

Question

I currently have an excel workbook that has an external data connection to a .txt file. This is in the form of a QueryTable.

What I'm trying to do is create a dialog box to ask the user for a folder of .txt files. I would then loop through those files, refreshing the query table with each new file. After the initial folder dialog, I don't want the user to see any other dialogs.

My current code is:

myQuery = ThisWorkbook.Sheets(1).QueryTables(1)
Filenames = Application.GetOpenFilename(MultiSelect:=True, Title:="Select files")

For Each Filename In Filenames
    myQuery.Connection = "TEXT;" & Filename
    myQuery.Refresh 'This displays a dialog box to choose a new file each time.
    ' Some other code that would execute after each refresh
Loop

What I'm looking for is something that will refresh the QueryTable with the current .Connection string, but will not open a new dialog on each refresh.

I've tried using Application.DisplayAlerts = False, but that does not suppress the Refresh dialog.

Any ideas to accomplish something similar would be appreciated.

Thanks @JoshuaRoss, that's exactly the property that I was looking for and overlooked apparently. Can you post this as an answer so I can mark it resolved? — ryankey721
– ryankey721, Commented Apr 13, 2015 at 18:26
No problem! I can't count how many times I've missed a property or method I needed and all it takes is another set of eyes! — Soulfire
– Soulfire, Commented Apr 13, 2015 at 18:33

Soulfire · Accepted Answer · 2015-04-13 18:32:58Z

1

You should set the .TextFilePromptOnRefresh property of the QueryTable to False.

answered Apr 13, 2015 at 18:32

Soulfire

4,3161 gold badge25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA: Set new external data connection and refresh without querying user

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related