Open "xlDialogChangeLink" Dialog with VBA/VB.Net
 

I want to open the "ChangeLink" Dialog in my Excel 2007 project. The user of
my program should have the ability to select the datasource for a pivottable.

Application.Dialogs(Excel.XlBuiltInDialog.xlDialog OpenLinks).Show() or
Application.Dialogs(Excel.XlBuiltInDialog.xlDialog ChangeLink).Show()


raise the following exception

"The show method of the dialog object can not be executed" (translated from
German error message)

Open "xlDialogChangeLink" Dialog with VBA/VB.Net
 

According to the Help entry for "Built-In Dialog Box Argument Lists", this
dialogs takes the arguments:
document_text1, document_text2, ..., read_only, type_of_link

You will need to research what they actually mean/expect.

NickHK

"THZS" wrote in message
...
I want to open the "ChangeLink" Dialog in my Excel 2007 project. The user
of
my program should have the ability to select the datasource for a
pivottable.

Application.Dialogs(Excel.XlBuiltInDialog.xlDialog OpenLinks).Show() or
Application.Dialogs(Excel.XlBuiltInDialog.xlDialog ChangeLink).Show()


raise the following exception

"The show method of the dialog object can not be executed" (translated
from
German error message)

Open "xlDialogChangeLink" Dialog with VBA/VB.Net
 

Thank you for your help.

I tried this in several variations of parametersets but I can't figure out
how it works. I always get the same error.

The macro-recorder method is useless here. It doesn't record the dialog,
only the result.




:-(

TZS

"NickHK" wrote:

According to the Help entry for "Built-In Dialog Box Argument Lists", this
dialogs takes the arguments:
document_text1, document_text2, ..., read_only, type_of_link

You will need to research what they actually mean/expect.

NickHK

"THZS" wrote in message
...
I want to open the "ChangeLink" Dialog in my Excel 2007 project. The user
of
my program should have the ability to select the datasource for a
pivottable.

Application.Dialogs(Excel.XlBuiltInDialog.xlDialog OpenLinks).Show() or
Application.Dialogs(Excel.XlBuiltInDialog.xlDialog ChangeLink).Show()


raise the following exception

"The show method of the dialog object can not be executed" (translated
from
German error message)

Open "xlDialogChangeLink" Dialog with VBA/VB.Net
 

I get a 1004 error if the workbook does not contain links.
If there are links, then this works:
Application.Dialogs(xlDialogOpenLinks).Show

However, I cannot get xlDialogChangeLink to work at all in XL2002.

NickHK

"THZS" wrote in message
...
I want to open the "ChangeLink" Dialog in my Excel 2007 project. The user
of
my program should have the ability to select the datasource for a
pivottable.

Application.Dialogs(Excel.XlBuiltInDialog.xlDialog OpenLinks).Show() or
Application.Dialogs(Excel.XlBuiltInDialog.xlDialog ChangeLink).Show()


raise the following exception

"The show method of the dialog object can not be executed" (translated
from
German error message)

Open "xlDialogChangeLink" Dialog with VBA/VB.Net
 

Okay, that worked for me too. But sadly that is not the dialog I really need.
I want to open the dialog that is displayed when you select
"Insert/Pivottable" on the Ribbon or the related dialog that shows up if you
select "Options/Change Pivottable Datasource" (all menuitems translated back
from German). What ist the correct XlBuiltInDialog constant here?

Thomas

"NickHK" wrote:

I get a 1004 error if the workbook does not contain links.
If there are links, then this works:
Application.Dialogs(xlDialogOpenLinks).Show

However, I cannot get xlDialogChangeLink to work at all in XL2002.

NickHK

"THZS" wrote in message
...
I want to open the "ChangeLink" Dialog in my Excel 2007 project. The user
of
my program should have the ability to select the datasource for a
pivottable.

Application.Dialogs(Excel.XlBuiltInDialog.xlDialog OpenLinks).Show() or
Application.Dialogs(Excel.XlBuiltInDialog.xlDialog ChangeLink).Show()


raise the following exception

"The show method of the dialog object can not be executed" (translated
from
German error message)

Open "xlDialogChangeLink" Dialog with VBA/VB.Net
 

The Solution:


Sub PivotTableInsert()
'Source 1: Herbers Exelforum (Verknüpfungsdialog aus VBA aufrufen)
'Source 2: 2007 Office System Document: Lists of Control IDs
Dim oCntr As Office.CommandBarControl
'12247 = PivottableInsert
'12250 = ChangeDataSource
oCntr = Application.CommandBars.FindControl(Id:=12247)
If oCntr.Enabled Then oCntr.Execute()
End Sub

"THZS" wrote:

Okay, that worked for me too. But sadly that is not the dialog I really need.
I want to open the dialog that is displayed when you select
"Insert/Pivottable" on the Ribbon or the related dialog that shows up if you
select "Options/Change Pivottable Datasource" (all menuitems translated back
from German). What ist the correct XlBuiltInDialog constant here?

Thomas

"NickHK" wrote:

I get a 1004 error if the workbook does not contain links.
If there are links, then this works:
Application.Dialogs(xlDialogOpenLinks).Show

However, I cannot get xlDialogChangeLink to work at all in XL2002.

NickHK

"THZS" wrote in message
...
I want to open the "ChangeLink" Dialog in my Excel 2007 project. The user
of
my program should have the ability to select the datasource for a
pivottable.

Application.Dialogs(Excel.XlBuiltInDialog.xlDialog OpenLinks).Show() or
Application.Dialogs(Excel.XlBuiltInDialog.xlDialog ChangeLink).Show()


raise the following exception

"The show method of the dialog object can not be executed" (translated
from
German error message)