Add to LinkSources

  • Thread starter Thread starter MPF
  • Start date Start date
M

MPF

Is it possible with VBA to add to LinkSources? I've found code to edit/
delete items, but nothing for adding.

Thanks,

Morgan
 
You could add a formula that links to a cell in another workbook.

Why would you want to do this?
 
Each week we need to add references to 6 or so files in a new
directory for that week. Just trying to automate the process since the
only thing that changes is the folder name.

Sorry for sounding dense, but if I just append the the locations to
cells in another worksheet, this will put the reference in the "Edit
Links" dialog?

Thanks,

Morgan
 
I don't understand what you mean by references here.

But those sources are the files that your formulas use.

I'm not sure how you'd have a formula that retrieved a value from a file in a
folder that doesn't exist.
 
Thanks for replying Dave, I appreciate it.

What I'm trying to do is "spoof" a reference to a file, not
necessarily to the content within.

I've got a worksheet with a list of file-names and a cell for the user
to enter the corresponding folder name to reference. From that, I want
to add to the Edit Links items via macro. Since I have no knowledge of
the structure of the content, I'm not trying to reference cell(s).

The primary reason for this is the referenced files are on a
SharePoint server and expecting the user to know the full URL for the
reference isn't realistic.

Since there appears to be no alias for the linked file, this may be an
exercise in futility.

Thanks,

Morgan
 
You could create a formula (you need to have a link to something) that tries to
retrieve a value from a bogus folder and bogus workbook in that folder (but the
sheetnames have to be correct, I think) and to a cell.

I don't use sharepoint, but I could use:

='C:\My Documents\Excel\[this doesn't exist.xls]Sheet1'!$A$1

I'll get a prompt to find that file, but I can dismiss that. The formula will
evaluate to #Ref!, but I can use edit|links|change source.

Have you thought of just creating a dummy workbook in a dummy folder and
pointing to that. Then you can just change the link to point to the real
workbook in the real folder.
 
Back
Top