Differing pasted link file paths?

I have a Summary Workbook that I paste links from other Client workbooks into. Depending on how I open the file, the linked file path pasted is different. The links also behave different when updating the original source file.

If I use a hyperlink in the Summary Workbook to open a Client work book, then copy and paste as a link into the Summary I get this file path:

='\\planet.com\files\Data\Midwest\Cincinnati\Benefits\USER DOCS\Smith,John\Bid Folders\[TestFile.xlsx]Bid Sheet'!AL6

When I update the source file opened from the hyperlink, the changes made appear instantly on the Summary page

If I go to the file browser and open the Client Workbook, then copy and paste as a link into the Summary Page I get this file path:

='F:\Cincinnati\Benefits\USER DOCS\Smith,John\Bid Folders\[TestFile.xlsx]Bid Sheet'!AL6

If I update the source file opened from the hyperlink, the changes do not appear instantly. I must save and the Client workbook and then manually update the links in the summary page. If I open the client workbook from the file browser then the linked information will update automatically.

I think I understand why its doing this, because depending on the file path excel kind of sees the same file as a 2 different files.

But why is it doing this?

Is there a way to make the hyperlinks file path the same the file path when opening from the file browser?

Or is there a way to open a file similarly to a hyperlink, but it would be opening like it does from the file browser? But not have to point to the file location each time?

I know this is long, but thank you in advance for any help.

I'll give this one a try, but it will have to wait until I get back to work where I have mapped network drives. I think the key is to read the contents of the windows clipboard, check to see if it has either "//" or ":" to see if it's a valid path, and then parse out the drive and read the addresses associated with the mapping. If it's "//" then just keep the contents of the clipboard, else substitute the mapping and write it back to the clipboard.

So instead of using CTRL-C to paste, you'd have to map this macro to some other key.

Thanks for taking a look at it. Im not sure how to go about creating that macro, I am very new to macros.

This is what I came up with. How you use this is copy the path. Then select the cell where you want to paste the translated path and execute the macro. Map the macro to some unused control key like CTRL-q. So select the link and copy, position the cursor and CTRL-q.

Please Login or Register  to view this content.

Wow Dflac that looks like a lot of work, thank you for giving it a shot, I really appreciate it.

When I tried to use it I got :

"Function GetText() As String
Dim clipboard As MSForms.DataObject <-Compile Error: User-defined type not defined on Function
Dim strContents As String

Set clipboard = New MSForms.DataObject
clipboard.GetFromClipboard
GetText = clipboard.GetText
End Function"

I am not sure if I am using it right. In my original question I was trying to come up with a workaround for something I ran in to on another macro. You might know the answer and then this problem might be null.

I have a Summary Workbook that shows all of the Client Projects that that are being worked on. Each Client Project is a separate workbook. Each row of the Summary has a pasted link of a defined range from a different Client Project. There is a hyperlink to each Client Project that the user can click and go to the file to make updates.

I created a macro that:
- asks the user where the linked file is located
- it then creates a hyperlink to the file
- then it follows(clicks) the hyperlink and copies the defined range
- then pastes as a link into the summary workbook

It all works fine when I do the steps manually, and when I was actually recording the macro. But when I go to run the macro it runs into a problem. I have isolated it to excel following the hyperlink.

If I click on the cell that contains the hyperlink, then the warning about making sure the file is from a trustworhty source comes up, I hit ok, and the file opens.

I recorded a macro of clicking the Hyperlink, everything works as it should.I get this code:

Please Login or Register  to view this content.

When I run the macro, I get the warning about making sure the file is from a trustworhty source comes up, I hit ok, but then the hourglass comes up, excel pauses for a moment and then nothing happens.

This is what is driving me crazy, everything else work fine, and this one tiny little thing is messing it up.

The files are all located on our shared network drive. Im thinking either thats the reason or maybe its an excel setting. Do you have any ideas?

The full code with all the steps I have is below:

Please Login or Register  to view this content.

Ah, there's a Microsoftism at work here. To use MSForms.DataObject, you have to include the Microsoft Forms Library. But Microsoft decided that they didn't need to include it anymore. So to get it, launch the VB editor, Go to Tools -> References, Click on the Browse button - this should take you to the system 32 folder. Then search for FM20.dll and click open. That will put the library on the list and select it for you.

I launched FM20.dll and the macro ran. It just pasted the link into the selected cell. It worked when I copied a "//" path, but not when I copied a ":F" path.

That should be "F:" not ":F"

Sorry thats what I meant. "F:"

Hmm. It worked for the two mapped drives I have here.

What should the macro be doing?