Excel built-in dialogs

Question

Does anybody know how to call the import data built-in dialog excel from a macro (vba). I've tried Application.Dialogs.Item(...).Show but I can´t find the right dialog.

Thanks in advance.

Answer 1

If you choose the Object Browser and search for say, xlDialogImportTextFile, you will get a list of possible dialogs.

EDIT: Perhaps something on these lines would suit:

'Allow user to select text file
sf = Application _
    .GetOpenFilename("Text Files (*.txt), *.txt")
If sf <> False Then
    'Open text file
    Workbooks.OpenText sf
End If

Answer 2

The closest I can find using the dialog system is:

Application.Dialogs(xlDialogImportTextFile).Show

You can get a reference to the command bar button (at least for me in both 2k3 and 2k7) via:

Set button = Application.CommandBars.FindControl(ID:=6262)

But calling the Execute method on the button fails. Sadly, the short answer seems to be that it's not possible.

You can add QueryTable objects by hand. While not an optimum path, you could design your own simple interface for selecting the source data.

Answer 3

I don't think there is a VBA equivalent, because in one case you are returning data to a worksheet, while in the other case, the data is put into a recordset in memory.

This kludge should pop up the dialog for you, however:

SendKeys "%ddd"