CODEWITHSUNDEEP
excelvbadialogbuilt-in
Alonso
Alonso

Reputation: 1109

Excel built-in dialogs

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. Please help.

Thanks in advance.

Upvotes: 1

Views: 1398

Answers (3)

dbb
dbb

Reputation: 2877

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"

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

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

Upvotes: 0

Tom Mayfield
Tom Mayfield

Reputation: 6275

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.

Upvotes: 2

Related Questions