Import External Data Wizard

[Guest]

I am trying to initiate the Import External Data Wizard using code. I'm using
Excel 2003. I've tried using the following code:
Dim ImportData As CommandBarButton
Set ImportData = CommandBars("Worksheet Menu Bar") _
.Controls("Data").Controls("Import External Data").Controls("Import
&Data...")
ImportData.Execute

The above code results in the following error.
2147467259 - Execute Failed

I also tried to use Application.Dialogs, but I was not able to find the
right dialog.

How can I accomplish this?

 

[Guest]

Hi,
What about using the built-in Dialogs instead:
Dim v
On Error Resume Next
v = Application.Dialogs(xlDialogImportTextFile).Show()
'check v value if user clikc ok or cancel (cancel-> false i believe)

 

[Guest]

I need to have more options other than text files. The import external data
wizard provides the ability to import data from many different sources.

 

[Guest]

Concerning the Execute method, i don't know why it doesn't work with this
particular built-in menu item.

Still, using the
Dialogs(...).Show
i would think you could call that particular dialog. In xl2k, you have 3
CommandButtons (New Web Query, New Database uery, and Import Text File). For
web queries it is xlDialogNewWebQuery, for text file it is
xlDialogImportTextFile, but i couldn't find it for database query.
Look in the online help and search 'Built-In Dialog Box Argument Lists';
that's where i found the 2 previous ones.

 

[Guest]

Thanks sebastienm for your imput.

I really need to use the Import External Data Wizard, because it has all of
the import options in one place. I'm trying to make the application easy for
the user to use and for it to be self intuitive . If I can't find a way to do
it from code, the users will have to import the data using the menu which
will have to be explained to some users.

Thanks again.

 

[Gustav]

Call another instance of Excel from current

I am trying to initiate the Import External Data Wizard using code. I'm using
Excel 2003. I've tried using the following code:
Dim ImportData As CommandBarButton
Set ImportData = CommandBars("Worksheet Menu Bar") _
.Controls("Data").Controls("Import External Data").Controls("Import
&Data...")
ImportData.Execute

The above code results in the following error.
2147467259 - Execute Failed

I also tried to use Application.Dialogs, but I was not able to find the
right dialog.

How can I accomplish this?

If it's only possible for your business-task, you can try to avoid this problem by using another instance of Excel.Application,
that is, "call another from current" (see Excel VBA-code below):

Sub test_ImportData_Execute()
 
	Dim anotherExcelApp As Excel.Application
	Dim wbk As Workbook
	Dim ImportData As CommandBarButton
 
	Set anotherExcelApp = New Excel.Application
 
	anotherExcelApp.Visible = True
 
	Set wbk = anotherExcelApp.Workbooks.Add
 
	Set ImportData = anotherExcelApp.CommandBars("Worksheet Menu Bar")_ 
	.Controls("&Data").Controls("Import External &Data").Controls("Import &Data...")
 
	ImportData.Execute
End Sub