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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >

[Tomkat743]

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

"NickHK" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" wrote:
> >
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

Complain to the boss!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Tomkat743" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
> >