Consulting

Results 1 to 5 of 5

Thread: Filepath to reference cell value

  1. #1
    VBAX Regular
    Joined
    Jul 2019
    Posts
    10
    Location

    Filepath to reference cell value

    Hi All,

    I am running a macro from Excel, part of which opens a specified word document.

    At the moment, the filepath for the said word document reads:

    C:\Users\jeremy.watson\*****\*****\*****\*****.docm

    The document exists as a shared document and therefore if sarah Jennings wants to run the macro, Jeremy.watson would need to be replaced with sarah.jennings!

    I hope that makes sense... The half solution I have come up with is asking the user to enter their username into a cell - and then having the filepath refer to that cell to inform the filepath.

    If the above makes any sense, could someone tell me if it is possible, and if so, what should my filepath look like if I want it to incorporate the value in cell D2 instead of having a fixed path that reads name.surname?

    Thanks in advance for anyone's time!

    Total Noob.

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Try:
    Sub test()
        Dim fPath As String, oShell As Object
        Set oShell = CreateObject("Shell.Application").BrowseForFolder(0, "Please pick a folder", 0)
        fPath = oShell.self.Path & "\*****\*****\*****\*****.docm"
        MsgBox fpath
    End Sub
    Semper in excretia sumus; solum profundum variat.

  3. #3
    VBAX Regular
    Joined
    Jul 2019
    Posts
    10
    Location
    Quote Originally Posted by paulked View Post
    Try:
    Sub test()
        Dim fPath As String, oShell As Object
        Set oShell = CreateObject("Shell.Application").BrowseForFolder(0, "Please pick a folder", 0)
        fPath = oShell.self.Path & "\*****\*****\*****\*****.docm"
        MsgBox fpath
    End Sub

    Hi Paulked,

    Thank-you for your response!

    Am I right in assuming you cannot refer to a cell when writing a path?

    e.g. "C:\home" & "D2" & "\word.docm"

    Thanks,

    Erinc

  4. #4
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Maybe
    "C:\home" & [D2] & "\word.docm"
    --Okami

  5. #5
    VBAX Regular
    Joined
    Jul 2019
    Posts
    10
    Location
    Quote Originally Posted by 大灰狼1976 View Post
    Maybe
    "C:\home" & [D2] & "\word.docm"
    --Okami
    Ah, it worked! So simple, thank-you!

    Resolved.

Posting Permissions

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