Prompt for variable

[benm]

I have a main spreadsheet that is linked to other Spreadsheets. I
receive these other spreadsheets daily (i.e. MMDDYYYY.xls). I would
like to have the main spreadsheet prompt me for file name and then
replace all links within the main spreadsheet to point to the filename
entered.

Thanks in advance for your feedback!

 

[Guest]

Below is a macro for changing all links in a spreadsheet.

Sub Change_Links(

Dim CurrLink, NextLink As Strin

'*****Option 1 Promt User for Path Name*****
NextLink = InputBox("Enter File Name and Directory"

'*****Option 2 Enter Path On Worksheet****
' NextLink = Range("FilePath").Valu

aLinks = ActiveWorkbook.LinkSources(xlExcelLinks

If Not IsEmpty(aLinks) The
For i = 1 To UBound(aLinks
If Right(aLinks(i), 12) <> "CMDRGEN1.XLA" The
CurrLink = aLinks(i
End I
Next
End I

ActiveWorkbook.ChangeLink Name:=CurrLink, NewName:=NextLink, Type:=xlExcelLink
WriteError = Fals

End Su

 

[Ken Wright]

Or if your version has it, try Edit / Links / Change Source