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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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?
 
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
 
Thanks again for the great solve. I already did a total replace on all my
path statements and it worked perfect Thanks again.
 
Complain to the boss!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top