Help adapting a file path

[richanor]

Hi, I am using a macro that somebody gave me to merge several workbooks into one. I have had to adapt a few things including the path where the workbooks are stored (obviously they will be stored in a different place on my PC than they were on his).

In the very top part of the VBA module are these lines:


Option Explicit

Const strFolderPath As String = "C:\Users\Rich\My Documents\Admin\Annual Leave"

Const strSummaryFile As String = "Annual Leave Tracker.xlsm"


I have changed the file path to what I think it is but it keeps throwing up an error. There are a number of files in this folder ("colleage name.xlsx") which are all being merged into "Annual Leave Tracker.xlsm". Is there anyway to just tell the macro to look in the same folder as the "Annual Leave Tracker.xlsm" file?

Thanks

Rich

 

[JLGWhiz]

You can get the correct file path like this. Activate an empty sheet then run this macro.

Sub t()
Range("A1") = Workbooks("Annual Leave Tracker.xlsm").Path
End Sub

 

[richanor]

great, thanks for your help!


Rich

 

[richanor]

Hi, I tried this, but the code above gives me a runtime error 9 - subscript out of range. Any ideas why?