search and open folder even if Path changes

Hi Everyone,

Is it possible to search for a file and open it even if the path where the folder is located changes.Like for example with the sample code below the "C:\Users\desmondm\Desktop" might be different.

Set del = Workbooks.Open("C:\Users\desmondm\Desktop\Group Accounts 2013-2014\March 2013\Accpac Reports\Academy-xpert for Paracon_FR.001.xls")

Regards
Desmond

Thanks for the quick reply.Below is a sample of my code now where should I place that code that you provided?Please bear with me cause im still trying to learn.So how would I use what you provided me with the code below?

Sub Update_Months()
Dim answer As Variant
answer = InputBox("Which month do you want to update?" & vbCrLf & "Ex:March=1,April=2")

Dim Ws1, Ws2

' open the source workbook and select the source sheet
Set del = Workbooks.Open("C:\Users\desmondm\Desktop\Group Accounts 2013-2014\March 2013\Accpac Reports\Academy-xpert for Paracon_FR.001.xls")
Set del_1 = Workbooks.Open("C:\Users\desmondm\Desktop\Group Accounts 2013-2014\March 2013\Paracon Report\2014 - XPERT.xlsx")
Set Ws1 = del.Sheets("Sheet1")
Set Ws2 = del_1.Sheets("Academy")
Select Case answer
Case 1
'Academy Income Statement
Ws2.Range("E22").Value = Ws1.Range("C23").Value
Ws2.Range("E26").Value = Ws1.Range("C27").Value
Ws2.Range("E28:E49").Value = Ws1.Range("C28:C49").Value
Ws2.Range("E57:E58").Value = Ws1.Range("C56:C57").Value
End Select
End Sub

Regards
Desmond

Thanx Patel for the reply but is its possible for me to make the macro link the cells just after selecting the two file? because the macro allows me to choose the files but nothing happens after i've chosen the files.

I cant seem to select the two files at the same time.Should I press 'CRL,Shift' to select the files?

Thanks alot it worked I appreciate your help I learned alot from you.My last question is: is there a way to make the macro work without the user having to select the file manually can it be made automatic?

Originally Posted by patel45

you sed how can I select automatically ? what's the criterium ?

ok thanks patel managed to complete the project but one last question is.is it possible for me to create loop to save me alot of time eg:
Ws2.Range("E22").Value = Ws1.Range("C23").Value
Ws2.Range("E26").Value = Ws1.Range("C27").Value
Ws2.Range("E28:E49").Value = Ws1.Range("C28:C49").Value
Ws2.Range("E57:E58").Value = Ws1.Range("C56:C57").Value

Is there a shorter way to do the above?