CODEWITHSUNDEEP
vbaexcel
ryankey721
ryankey721

Reputation: 53

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

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.

Upvotes: 0

Views: 2157

Answers (1)

Soulfire
Soulfire

Reputation: 4296

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

Upvotes: 1

Related Questions