How to detect drive Letter and Open file

[Darwin123456]

Hi ,

I have some Vba that I am running in a workbook to open another workbook. However my colleagues have different drive letters so when they run the code they get an error, for example the file for me has fileparh Y:\Test\Test.xlsx however for my colleague it is Z:\Test\Test.xlsx.

Is it possible to detect what drive letter they are using and update the file path so they can use the code to open the other workbook?

Thanks in advance for any advice provided.

 

[rlv01]

Too much is unknown about your setup to be definitive, but here's one simple way so long as you can depend upon the VBA CurDir statement to tell you what their drive letter is.

    Dim OldPath As String, NewPath As String, DriveLetter As String
    
    OldPath = "Y:\Test\Test.xlsx"
    DriveLetter = Left(CurDir, 1)
    NewPath = DriveLetter & Mid(OldPath, 2, Len(OldPath))

 

[Snakehips]

@Darwin123456 Welcome.

Maybe set a string variable eg MyPath to modify the user's file path to the parent workbook.
Eg if the parent file is open in Test folder then just

MyPath = ThisWorkbook.Path & "\Test.xlsx"

 

[Darwin123456]

Thank you both, I'll give them solutions a try.