Calling cell value in VBA

What I am trying to do:

I have a code which extracts sentences from a word document and separates them into rows on an spreadsheet. The document is saved on my network drive (S:) which has my username in the directory. I wish to set up a macro which can be run by multiple users. To do this I am trying to build in to my code a call of the username (which will be entered into cell A1 of Sheet1). The script I have is below, section I cant work out is under 'Word document object.

Sub Sentence_Retrieve()

    Dim objWord As Object

    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True

    'Word document object
    Dim WordNam As String
    WordNam = "S:\verytallmike\Desktop\Sentence_test.docx"

    'Open word document
    objWord.Documents.Open WordNam
End sub

Any thoughts on how I can call the cell value from A1 Sheet1 into where it currently says 'verytallmike'?

Solution

You will need to create a variable to hold the value in cell A1 and use some basic string concatenation to create a full path to the file. This example is very basic:

Dim directoryPart as String
directoryPart = Worksheets("Sheet1").Range("A1").Value

' combine the value from A1 to form a full path to the file
Dim filePath As String
filePath = "S:\" & directoryPart & "\Desktop\Sentence_test.docx"