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)

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)

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)

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)

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)

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)