none
Excel VBA Replace Issue RRS feed

  • Question

  • In Excel VBA, I'm trying to use the REPLACE function to change a link to point to another folder.

    The original looks like this:

    ='https://mysite.sharepoint.com/Shared Documents/2016 Projects/[Project Package.xlsm]Cover'!$D$4

    I want to change the /Shared Documents/2016 Projects/ to /Archived/

    with the result looking like this

    ='https://mysite.sharepoint.com/Archived/2016 Projects/[Project Package.xlsm]Cover'!$D$4

    It is not working.  It does work with other cells but their strings have " (double quotes) around the text and it works fine.

    Therefore, I believe it has something to do with the original string having a ' (single quote).  Is there a way to make the REPLACE work?

    Thanks,

    AGI_MEG


    • Edited by AGI_MEG Wednesday, October 12, 2016 1:03 PM
    Wednesday, October 12, 2016 1:00 PM

Answers

  • Guess you use VBA Replace function. Not Excel Worksheet function.

    And probably what you pasted is a formula of excel cell.Say in A1 the formula is there. Below will put updated formula in A2

    Sub LittleMcro()
    
    Range("A2").Formula = Replace(Range("A1").Formula, "/Shared Documents/", "/Archived/")
    
    End Sub
    
    If that does not help, pls give us more detail.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, October 12, 2016 4:28 PM
    Answerer

All replies

  • Guess you use VBA Replace function. Not Excel Worksheet function.

    And probably what you pasted is a formula of excel cell.Say in A1 the formula is there. Below will put updated formula in A2

    Sub LittleMcro()
    
    Range("A2").Formula = Replace(Range("A1").Formula, "/Shared Documents/", "/Archived/")
    
    End Sub
    
    If that does not help, pls give us more detail.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, October 12, 2016 4:28 PM
    Answerer
  • Thank you for your assistance.

    That method using the RANGE worked.

    I tried to respond to the thread last week, but the system wouldn't let me... not sure.

    Again, thanks for the help.

    AGI_MEG

    Tuesday, October 18, 2016 9:13 PM