Linked Workbooks
 

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

Linked Workbooks
 

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

phuser wrote:
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

Linked Workbooks
 

Hi P,

Try:

Application.Dialogs(xlDialogOpenLinks).Show


---
Regards,
Norman


"phuser" wrote in message
...
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

Linked Workbooks
 

Hi Something,

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


---
Regards,
Norman


"somethinglikeant" wrote in message
ups.com...
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

phuser wrote:
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

Linked Workbooks
 

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.

"somethinglikeant" wrote in message
ups.com...
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

phuser wrote:
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

Linked Workbooks
 

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

If Not IsEmpty(ActiveWorkbook.LinkSources(xlExcelLinks)) Then

End If

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.

---
Regards,
Norman



"phuser" wrote in message
...
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.

"somethinglikeant" wrote in message
ups.com...
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

phuser wrote:
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

Linked Workbooks
 

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" wrote in message
...
Hi P,

Try:

Application.Dialogs(xlDialogOpenLinks).Show


---
Regards,
Norman


"phuser" wrote in message
...
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

Linked Workbooks
 

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.


---
Regards,
Norman

"phuser" wrote in message
...
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" wrote in message
...
Hi P,

Try:

Application.Dialogs(xlDialogOpenLinks).Show


---
Regards,
Norman


"phuser" wrote in message
...
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

Linked Workbooks
 

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

Thanks for all your help.

P

"Norman Jones" wrote in message
...
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.


---
Regards,
Norman

"phuser" wrote in message
...
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" wrote in message
...
Hi P,

Try:

Application.Dialogs(xlDialogOpenLinks).Show


---
Regards,
Norman


"phuser" wrote in message
...
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