Linked Workbooks

[phuser]

I would like to create a Macro that brings up the edit links dialogue box,
every week the reference to a different workbook
needs to be changed, if I go through the record macro, of course the
workbook I chose in the record is the one it changes to. I just need the
dialogue box to appear and the user can choose the workbook they wish to
link.

Thanks

 

[somethinglikeant]

The following code will display the dialog box you require.
Your decision now is how you choose to execute this code.
Maybe it would be suitable in a Workbooks Before Open Mode at a certain
time in the week?
Let me know if you need further help.

Sub ShowDialog()
If Not IsEmpty(ActiveWorkbook.LinkSources(xlExcelLinks)) Then
Application.Dialogs(xlDialogOpenLinks).Show
End If
End Sub

somethinglikeant

 

[Norman Jones]

Hi P,

Try:

Application.Dialogs(xlDialogOpenLinks).Show

 

[Norman Jones]

Hi Something,

Apologies, I did not see your response when I posted!.

 

[phuser]

I get an error "Show Method of Dialog Class Failed" for both formula's, I
pasted the formulas to an already created Macro
that is generated from a command button in the Workbook.

 

[Norman Jones]

Hi P,

I get an error "Show Method of Dialog Class Failed" for both formula's, I
pasted the formulas to an already created Macro
that is generated from a command button in the Workbook.

Did you include the

lines?

If there are no links, you will.encounter this error unless you test
pre-emptively for the existance of links.

That is exactly that SomethingLikeAnt's code does.

 

[phuser]

I still recieve the msg.
I'll post the entire macro, maybe it's something in the sequence.

Sub ClearSave()
'
'Retrieve file name to use for Save
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")

'If user specified file name, perform Save and display msgbox
If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal

MsgBox "Save as " & fileSaveName
End If


If Not IsEmpty(ActiveWorkbook.LinkSources(xlExcelLinks)) Then
Application.Dialogs(xlDialogOpenLinks).Show
End If


Sheets("PA01").Select
Range("C7").Select

End Sub

 

[Norman Jones]

Hi P,

I cannot reproduce your error.

Running the code, if there are workbook links, the dialog is shown; if not,
no dialog is shown,

In either case, I do not encounter your error.

 

[phuser]

Guess What ;-) the worksheets were grouped, as soon as I ungrouped them it
worked.

Thanks for all your help.

P