Excel VBA - How to write filepath as a variable-CodePudding

I have a file saved in location A with path link C:\User\Testing\A\client_01.xlsm But I need to open the file in location B eg. C:\User\Testing\B\client_01.xlsm where the location saved the related documents in there. sometimes open in location C \ D etc. So the path link save in macro needs to be variant as the location changes

Please advice as I'm beginner in VBA

Thanks

Sub Testing

Dim FilePath As String
Dim FileName As String
Dim wb As Workbook
Set wb = ThisWorkbook

FileName = wb
FilePath = "C:\User\Testing\",FileName,"\client_01".xlsm"

End Sub

CodePudding user response：

FileName is a string so you'll have to use wb.Name

As for the FilePath, you can't concatenate like that, use & instead FilePath = "C:\User\Testing\" & FileName & "\client_01.xlsm"

That is ofc if the workbook your code is in is called "A", "B", "C" or whatever you wanted.

If you want to use the path of the workbook that you're code is in: use something like:

Sub Testing

    Dim FilePath As String
    Dim FileName As String
    Dim wb As Workbook
    Set wb = ThisWorkbook

    FileName = wb.Name
    FilePath = ThisWorkbook.Path & "\" & FileName & "\client_01".xlsm"
End Sub

You can adjust as you see fit ofc

CodePudding user response：

To return the current workbook's folder path as a string, use:

FilePath = wb.Path

Then if your code opens to related documents (lets say their names are saved as string variables relateddoc1 etc):

Workbooks.Open(FilePath & "\" & relateddoc1)