+ Reply to Thread
Results 1 to 11 of 11

Update filepath in a cell with VBA

  1. #1
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    177

    Update filepath in a cell with VBA

    Hi,

    I have a 4 cells which contain the followng information, To: Email address, Subject: Text, Body: Text, File Path: File path (as Hyperlink).

    I need the file path cell to update automatically when the file is saved somewhere new. This filepath is then used in an email that is generated with code so that the person opening the email can click on the link and open the file.

    What I cannot seem to do is to get the file path to be something that updates automatically (which is functionality that I must have)but that also remains as a hyperlink for when it goes in the email (I have an additional problem at the moment which is that the hyperlink does not seem to work in the email - it keeps saying file cannot be found. I don't know if this is something to do with our systems and the way I am referring to the file?!?)

    The cell with the file path currently loks like this:-

    Cell AX Cell AY
    File path file:///E:\PART_TIME_LEAVE\PART_TIME_PS_LEAVE_RECORD_EMAIL_VERSION_STATUTORY.xlsm

    The code that generates the email looks like this:-

    Please Login or Register  to view this content.

    Any help greatly appreciated!

    Many thanks
    D

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Update filepath in a cell with VBA

    What cell is the actual filepath in?

  3. #3
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    177

    Re: Update filepath in a cell with VBA

    Hi,

    It is in cell AY6.

    Thanks
    D

  4. #4
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Update filepath in a cell with VBA

    If you make a change to the hyperlink after the document is saved it is going to prompt you to save the workbook again. Will the hyperlink always be for the workbook the email is sent from?


    Instead of inputting a hyperlink based on a cell if you change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    it will insert a hyperlink based on wherever the workbook you called the macro from is currently saved.

    This will prevent you from having to have a custom save macro.
    Last edited by Solus Rankin; 03-21-2014 at 09:47 AM.

  5. #5
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    177

    Re: Update filepath in a cell with VBA

    When I click on the link in the email it still says cannot find file

  6. #6
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Update filepath in a cell with VBA

    Is it open before you click on it?

  7. #7
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    177

    Re: Update filepath in a cell with VBA

    No it is not open

  8. #8
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Update filepath in a cell with VBA

    Where did you save the workbook? Are you sure you have access to where you saved it?

  9. #9
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    177

    Re: Update filepath in a cell with VBA

    It is on a local network drive and yes I have access.

  10. #10
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Update filepath in a cell with VBA

    Try this:
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    03-10-2014
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    177

    Re: Update filepath in a cell with VBA

    That works perfectly!! Thank you sooooooo much!

  12. #12
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Update filepath in a cell with VBA

    Glad to help!

    Thanks for the rep and for marking the thread as [SOLVED].

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Trying to get the filepath on "Open", but get filepath of a temporary file instead
    By ianpwilliams in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-22-2013, 12:27 AM
  2. Use cell values to determine filepath/links
    By Krandamor in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-06-2012, 11:19 AM
  3. [SOLVED] Copying files from a selected filepath to a filepath mentioned in a worksheet's cell
    By subbby in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-10-2012, 03:59 PM
  4. [SOLVED] Copying filepath onto a cell
    By subbby in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-01-2012, 11:43 AM
  5. Filepath in cell problem.
    By Gus80 in forum Excel General
    Replies: 2
    Last Post: 03-31-2010, 07:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1