What does no response mean???? Am I asking a stupid question???

[Tomkat743]

The following Macro works fine as long as I don't change the location of the
template files. when I copy the blank folder to the weekly folder at say,
C:\Documents and Settings\Tom\Desktop\CHARTER\WK 15 I lose the ability in my
macro to open the week 15 files. It becomes more complicated in that I use
Microsofts Groove Networks to form a file sharing workspace between offices
and my laptop so when ever I need a local office to run the macro I have to
go in and edit each macro for the right location and because I have 14 Macros
for each week it becomes tedious to setup and maintain. The following is
just one of many lines of code that use the file location. I know I could set
up a C:/Data folder structure on every computer but I was rather hoping I
could tell the Macro to be flexible. My initial thought was that if I saved
the Macro in the workbook instead of personal.xls that when I moved the
workbook the Macro would change with the formulas. Any help
would be appreciated, Thanks.
Dim bkList As New Collection
With Workbooks("1DLSUNDAY.XLS").Worksheets("Master")
Set rng = .Range(.Cells(2, 3), .Cells(200, 3).End(xlDown))
End With

On Error Resume Next
For Each cell In rng
if len(trim(cell)) > 0 AND val(trim(cell)) <9521 AND val(trim(cell)) >0
Then
bkList.Add Trim(cell.Text), Trim(cell.Text)
end if
Next
On Error GoTo 0

For Each itm In bkList
Workbooks.Open "C:\Documents and Settings\Tom\Desktop\CHARTER BLANK\NEW
BLANK\" & _
itm & ".xls"
Next

 

[NickHK]

Tomkat,
Not sure if this is what you mean, but you can use soemthing like;
Workbooks.Open ThisWorkbook.Path & "\" & itm & ".xls"

to keep everything relative the WB that contains the running code.

NickHK

 

[Tomkat743]

Thank you its very odd that I had to post this question 3 times under
different headings to get a response.

Will this same path statement work every where I had to put a path statement?

 

[NickHK]

Provided you have saved the WB once, ThisWorkbook.FileName will always be
valid.
Whether it is the correct path for your purposes, only you can tell.

NickHK

 

[Tomkat743]

Thanks again for the great solve. I already did a total replace on all my
path statements and it worked perfect Thanks again.

 

[Bob Phillips]

Complain to the boss!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)