VBA file path

[mel13028]

I'm new to VBA and writing a code that will go through a list of excel files and update the date to match the current excel file. I want to make it so that we don't have to write the full file path including what drive its on, just match it to the folder that the files are contained in so that we can just put filename.xlsx in the list. This is my code so far. Any ideas?

Dim Num As Double
Num = 8
Dim FilePath As String
Dim Number As Double
Number = 8
Do Until Cells(Num, 1).Value = ""
If Cells(Num, 1).Value = "Yes" Then


FilePath = ActiveWorkbook.Sheets("Sheet1").Range("B" & Number).Value
Workbooks.Open Filename:=FilePath
ActiveWorkbook.Sheets("Sheet1").Range("B2") = "=DATE_macro.xlsm!ValDate"
ActiveWorkbook.Save
ActiveWorkbook.Close
Number = Number + 1
Num = Num + 1
ElseIf Cells(Num, 1).Value = "No" Then
Num = Num + 1
Number = Number + 1


End If
Loop




End Sub


Thanks!