How do I prevent Excel 2010 from changing my drive designation in a macro?

I am running the ChangeHksyperlinks macro to change the drive from C to a shared drive, G.  When I run the macro, the new hyperlink shows ///\\shared\groups\ instead of the G drive and of coursele the files won't open.
Can you help?  I have 80 hyperlinks to change.....
caldrich57Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Can you share the code which you  are using to change the path? Or you want us to write a code for you to do this??

Saurabh...
0
caldrich57Author Commented:
This is the code I am using:
Sub ChangeHyperlinks()

 Dim h As Hyperlink
 Dim oldDr As String, newDr As String
 
oldDr = "C:\Project Hyperlink Files\"
 newDr = "G:\IS\IS Shared Data\BI\2015 BI Assessment Survey\Membership Report Examples\Project Hyperlink Files\"
 For Each h In Sheets("Sheet1").Hyperlinks
 h.Address = newDr & Mid(h.Address, Len(oldDr) + 1, Len(h.Address))
 Next h
 End Sub

When I run this as shown, the new drive shows in the hyperlink as ///\\shared\groups\IS\IS Shared Data\BI..... etc. etc.
It is a shared drive I am moving the links to but shouldn't it still show as G:\IS\IS Shared Data, etc. as I keyed it?
The files will not open after I run the macro but the hyperlinks do change.
0
Saurabh Singh TeotiaCommented:
Sub ChangeHyperlinks()

 Dim h As Hyperlink
 Dim oldDr As String, newDr As String
 
oldDr = "C:\Project Hyperlink Files\"
 newDr = "G:\IS\IS Shared Data\BI\2015 BI Assessment Survey\Membership Report Examples\Project Hyperlink Files\"
 For Each h In Sheets("Sheet1").Hyperlinks
h.Address = Replace(h.Address, oldr, newDr)
 Next h
 End Sub

Open in new window


Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

caldrich57Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for caldrich57's comment #a40906768

for the following reason:

I don't know why but this code did not change the links.  No error message received, it just didn't do anything.
0
Saurabh Singh TeotiaCommented:
caldrich57,

I just ran the code before giving to you with a dummy path and it seems to work correct for me..Can you help me understand what happens post when you the code?

Saurabh...
0
caldrich57Author Commented:
Nothing happens.. the hyperlinks don't update and I receive no error message.  Very odd....
0
Saurabh Singh TeotiaCommented:
When you click on edit hyperlink what path does it show to you??
0
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
Saurabh Singh TeotiaCommented:
The solution which i have provided is a working solution..Their is something which is he doing as a result not able to see hyperlinks updated in the correct format. As you can test it in the sample file and it does what you are looking for..

Saurabh...
0
Saurabh Singh TeotiaCommented:
The solution which i have provided is a working solution..Their is something which is he doing as a result not able to see hyperlinks updated in the correct format. As you can test it in the sample file and it does what you are looking for..

Saurabh...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.