Moved workbooks to folders now User form "Subsript out of range"

[cheelie]

Hi Guys
I'm sure this must be an easy fix and I'm sure I'll look like a fool once It's resolved!
The attached workbook has a userform which was searching the same folder where this file is located for entries in columns "A" and "B" of files named in column "I" (workbook1, workbook2 etc).
I have this placed on a sharepoint and want to move the files into specific folders (folder1/workbook1, folder2/workbook2 etc.) so I can set them as view only folders to stop unuathorised editing.
I am now getting a "Subscript out of range" error after doing a search. I can see it's a problem with the file name as it's pulling it from text in a cell but can't work out a fix!

Thanks!!

[royUK]

Your code expects the files to be in the same folder as the maon workbook, see

 Workbooks.Open ThisWorkbook.Path & "/" & c.Value & ".xls"

You need to change ThisWorkbook.Path

[cheelie]

Hi Roy

Thanks for your reply.
The workbooks open method seems to work Ok as the I've changed the cell value it's referencing to reflect the new path....from workbook1 ".xls" to folder1\workbook1 ".xls"; it seems to screw up here:

Set ws = Workbooks(c.Value & ".xls").Worksheets(1)

Thanks

[royUK]

Have you change the ThisWorkBook part in the code?

[cheelie]

Hi Roy

No not changed this workbook path...am a little confused!!
This is on a sharepoint and as the codes stands I can see a dialogue box open and it is openeing the file from within the new folder with the new path name visible....this is why I assumed this part of the code was OK!!

[royUK]

Have you tried Dave's amended code?

[davesexcel]

Could be this

         Workbooks.Open ThisWorkbook.Path & "/" & c.Value & ".xls"

Should be something like this?

         Workbooks.Open "C:\Documents and Settings\Charlotte\My Documents\Admin\Payroll\Time Sheets\" & "/" & c.Value & ".xls"'

What I do is use windows explorer, that shows the folder address, then copy and paste it into the code