complicated macro

[SteveDB1]

Hi all.
I have a workbook that I'd like to link to 30 to 35 other workbooks so that
it will update each time it's opened, to the most recent versions of those 30
to 35 workbooks.
I.e., the files will typically have the same names as the "old" ones, and
will be replaced every few weeks.
So, to see if I can clarify this....
Workbook A presently contains worksheets from workbooks WB1 through WB35.
Each time Workbook A is opened, with the linked worksheets, it gives a
message stating that worksheets within the workbook are linked to other
workbooks, and asks if the user wants to update those links.
However, at least a couple of the workbooks WB1 through WB35 have been
changed/modified since the last time WorkbookA was opened. In fact, it would
be a different file than it was before-- e.g. a few different people have
worked on it, changes have been made, and it now resides, as a replacement of
one of the original workbooks WB1 through WB35. E.g. WB1 could now be
WB1-August2007, to show that it's been changed since last being worked on.
My want is to have the macro, or link tool go through the specified
directory that we store our "published" workbooks, and look for the
replacement workbook, and update the data that's been changed from the old to
the new.
1- how would this be accomplished?

I suppose that I should state that the ultimate goal will be to place all of
the data into our Access database. And hopefully have it update
"automatically" as well.

My initial thoughts were to come up with a macro that will check if the last
modified date is earlier, or later than the last time the workbookA was
saved. If it was later open the "source" workbooks, WB1.... WB35, look at the
information on the desired (specified by one of two "catch" words) 'A' or 'B'
worksheet (then look for a specific phrase that is on each worksheet (123),
within its specified workbook- WB1....WB35) replace that specific worksheet
with the newest version, and then rename the replacement worksheet to the
correct name- determined by the "catch" phrase/word mentioned above.
So, it seems to me that stating the source directory, on a specific server
would be easily enough done. Something similar to:
Workbooks.Open "S:\Assignments Final\TRC\..."

I'd then need to have a "search" component for the workbook, and then one
for the worksheet, and then for a specific phrase, or word on the chosen
worksheet.
Once it identifies the worksheet correctly, it'd need to copy the worksheet
into the workbookA. Once the copy function is complete, I'd need to have it
rename the worksheet to the specified word- 123, 122, 124, etc.....
If more specific information is needed, please ask.
In advance, as always, thank you for your time, and willingness to help.
Best.

 

[Joel]

I think I would do things different than your recommendations. I would have
two directories. One a working directory where the users update the files
with the date included. I would have a second master directory that would
have the files without any date which would be the latetest files.

I would then create a workbook that just contained the macros needed to
update the files. This workbook would copy the latest files from the working
directory into the master directory and then open Workbook A. The links
would never have to change in workbook A.

 

[SteveDB1]

Joel,
Actually, that's what we have.
first directory is a working directory-- multiple directories, one for each
user, there are 7 users, so we have 7 "working" directories.

The Second directory is a master directory-- one directory for all files,
placed there only by one of two people who are responsible for verifying the
data in each workbook, and once verified, the workbooks are placed in the
final master directory (Generally speaking the files are renamed to a more
basic name, half of which is the original workbook's name).

One of my colleagues began giving his file names the dated addons to make it
clear that they were the most recent version. The dates appear to be removed
when placed in the Master directory. It made sense, so I started doing it
too. I.e., sometimes we can get upwards of 7 to 10 working files in the
various working directories because of how quickly things can move around
here.

Workbook A is still in initial set up stages.
At present, I've taken copies of the specific worksheets that I want from
each of the 35 workbooks I mentioned and placed them into WorkbookA. These
would be my "baseline" worksheets.
From this point forward, I'd look in the master directory only for the
specific 35 workbooks. The primary search parameter would be the file name
(ABC-X31... ABC-X66), and the second search parameter would be the most
recent "save" date being newer than the last save date of WorkbookA (my
preference is that it'd look at a save date being newer than the specific
worksheet, but my understanding is that MS does not allow for that. Unless I
could have it look at the footer strings placed there by the user when they
last printed the specific worksheet-- which would in fact work for my
purposes[question is: can that be done?].).

I've been tinkering with the idea I've got to replace worksheets, instead of
just linking the data in the worksheets. The link seemed to be too easily
broken, and could quickly cascade to a major problem.

Since I'm looking through a number of workbooks, it seems I need to start
with a FOR,to,next loop.

Within the for loop, I'd need to have a series of nested IF statements
looking for true/false items.
IF(stringName =
workbookName,(IF(date>LastSaveDate,workbook.open,ignore),ignore)
Where the stringName would be the file name that I'm looking for.
The date would be the appropriate code-- which while I think it does exist,
I'm not familliar with.

Once the workbook is open, I'd want to look for a specific worksheet named
'A' or 'B.' Once I found one of those two, I'd take a copy of the worksheetA,
or worksheetB, and paste it into my existing workbookA.

If my understanding of the language is correct, that'd be worksheet.copy,
and then worksheet.paste.

After the new worksheet is located within workbookA, I'd want to rename it
by looking on the second line of that worksheet for the predetermined
number-- X31.... X66. Once I found that, it'd be placed in the worksheet name
using a rename command.
Then, so that I don't keep "duplicating" worksheets, it'd need to look at
all of the worksheets (in workbookA) to see if an existing worksheet exists.
If it does exist-- and it should-- delete the existing one, and replace it
with the new.

In explaining this to one of my colleagues, he said that he could readily
identify further uses for it with his own workbooks, and independent
directories.
So.... as I think about this.
1- Does this sound correct/feasible?
2- what are some of the commands that I'd need to perform the tasks that I'm
seeking to accomplish?
such as:
most recent Date saved search?
comparison date search?
string search for a group of file names, one file name at a time? (I suppose
that I need to state here that there are some 750 files in the master
directory, and I only want 35 of them (that are located) in the middle of
that directory.)
workbook.open (X31....X66)
worksheet.copy('A' OR 'B')
worksheet.paste('A' OR 'B')
worksheet.delete((old) X31....X66)
worksheet.rename('A' OR 'B' to (new) X31....X66)
While there is a lot more that I'd like this macro to perform, I think that
this is enough for the time being to just get it up and going. Once this part
is working, I can come back for more.
I hope this is better explained this time. If not, please ask.
Thank you, in advance.
Best.

 

[Joel]

I recommend that you name the files in the working directory
file1_2007_07_03.xls Make sure month and day are two digits. When you sort
the files alphabetically the latest file will be the last file. the files
in the master directory will be just the base name file1.xls

The code below finds all the files names in the master directory. Then
searches for the latest file in the working directory with the same base
name. Finally copies the lastest file in the working direcory to the master
directory and overwrites the old file in the master directory.



Sub updatefiles()

Master = "C:\Temp\Master\"
Working = "C:\Temp\Working"

Set fso = CreateObject _
("Scripting.FileSystemObject")

First = True
Do While (1)
If First = True Then
MasterFile = Dir(Master)
First = False
Else
MasterFile = Dir()
End If

If MasterFile = "" Then Exit Do

'strip off extension
MasterFileBase = Left(MasterFile, _
Len(MasterFile) - 4)
Set fs = Application.FileSearch
With fs
.LookIn = Working
.Filename = MasterFileBase & "*.xls"
If .Execute > 0 Then

'get latest filename
LastestFile = .FoundFiles(1)
For i = 2 To .FoundFiles.Count
If StrComp(.FoundFiles(i), _
LastestFile, vbTextCompare) > 0 Then

LastestFile = .FoundFiles(i)
End If
Next i

'copy lastest file to master directory

fso.CopyFile LastestFile, _
Master & MasterFile


Else
MsgBox "File " & MasterFileBase & ".xls" & _
" Not found in Working Directory"
End If
End With
Loop

End Sub

Joel,
Actually, that's what we have.
first directory is a working directory-- multiple directories, one for each
user, there are 7 users, so we have 7 "working" directories.

The Second directory is a master directory-- one directory for all files,
placed there only by one of two people who are responsible for verifying the
data in each workbook, and once verified, the workbooks are placed in the
final master directory (Generally speaking the files are renamed to a more
basic name, half of which is the original workbook's name).

One of my colleagues began giving his file names the dated addons to make it
clear that they were the most recent version. The dates appear to be removed
when placed in the Master directory. It made sense, so I started doing it
too. I.e., sometimes we can get upwards of 7 to 10 working files in the
various working directories because of how quickly things can move around
here.

Workbook A is still in initial set up stages.
At present, I've taken copies of the specific worksheets that I want from
each of the 35 workbooks I mentioned and placed them into WorkbookA. These
would be my "baseline" worksheets.
From this point forward, I'd look in the master directory only for the
specific 35 workbooks. The primary search parameter would be the file name
(ABC-X31... ABC-X66), and the second search parameter would be the most
recent "save" date being newer than the last save date of WorkbookA (my
preference is that it'd look at a save date being newer than the specific
worksheet, but my understanding is that MS does not allow for that. Unless I
could have it look at the footer strings placed there by the user when they
last printed the specific worksheet-- which would in fact work for my
purposes[question is: can that be done?].).

I've been tinkering with the idea I've got to replace worksheets, instead of
just linking the data in the worksheets. The link seemed to be too easily
broken, and could quickly cascade to a major problem.

Since I'm looking through a number of workbooks, it seems I need to start
with a FOR,to,next loop.

Within the for loop, I'd need to have a series of nested IF statements
looking for true/false items.
IF(stringName =
workbookName,(IF(date>LastSaveDate,workbook.open,ignore),ignore)
Where the stringName would be the file name that I'm looking for.
The date would be the appropriate code-- which while I think it does exist,
I'm not familliar with.

Once the workbook is open, I'd want to look for a specific worksheet named
'A' or 'B.' Once I found one of those two, I'd take a copy of the worksheetA,
or worksheetB, and paste it into my existing workbookA.

If my understanding of the language is correct, that'd be worksheet.copy,
and then worksheet.paste.

After the new worksheet is located within workbookA, I'd want to rename it
by looking on the second line of that worksheet for the predetermined
number-- X31.... X66. Once I found that, it'd be placed in the worksheet name
using a rename command.
Then, so that I don't keep "duplicating" worksheets, it'd need to look at
all of the worksheets (in workbookA) to see if an existing worksheet exists.
If it does exist-- and it should-- delete the existing one, and replace it
with the new.

In explaining this to one of my colleagues, he said that he could readily
identify further uses for it with his own workbooks, and independent
directories.
So.... as I think about this.
1- Does this sound correct/feasible?
2- what are some of the commands that I'd need to perform the tasks that I'm
seeking to accomplish?
such as:
most recent Date saved search?
comparison date search?
string search for a group of file names, one file name at a time? (I suppose
that I need to state here that there are some 750 files in the master
directory, and I only want 35 of them (that are located) in the middle of
that directory.)
workbook.open (X31....X66)
worksheet.copy('A' OR 'B')
worksheet.paste('A' OR 'B')
worksheet.delete((old) X31....X66)
worksheet.rename('A' OR 'B' to (new) X31....X66)
While there is a lot more that I'd like this macro to perform, I think that
this is enough for the time being to just get it up and going. Once this part
is working, I can come back for more.
I hope this is better explained this time. If not, please ask.
Thank you, in advance.
Best.

I think I would do things different than your recommendations. I would have
two directories. One a working directory where the users update the files
with the date included. I would have a second master directory that would
have the files without any date which would be the latetest files.

I would then create a workbook that just contained the macros needed to
update the files. This workbook would copy the latest files from the working
directory into the master directory and then open Workbook A. The links
would never have to change in workbook A.

 

[SteveDB1]

Joel,
Thanks for your response.
It's not my goal to replace workbooks in the master directory with workbooks
from the working directory. There are others above me that are responsible
for that part of our work.
My goal was to have a master workbook-- workbookA, with a specific set
(before they are moved into workbookA, they were named either 'A' or 'B') of
worksheets (already in workbookA, as of yesterday afternoon, with new names
for each worksheet-- 231 through 266).
These specific worksheets are just one of 35 worksheets within the master
directory workbooks.
I am looking to copy worksheets from the master directory workbooks-- I'd
said 35 or so out of the 700+ we have in our master directory--

I want to check for the most recently saved workbooks, compare those dates
with the last save date of workbook A (wbA), and if the workbooks in the
master directory (within the range of 231 to 266-- out of 1 to 750) have been
saved since the last wbA save, I want to copy one specific sheet over to my
wbA.

I will however tinker with your code and see how it works for me. I know of
least two people at work who would use something like this.
We finally got our copy of the new "Excel 2007 VBA programmer's reference"
book yesterday afternoon, so I'll be busy reading for the next few weeks.
Thanks again for your willingness to answer my questions, and request for
help.
Best

I recommend that you name the files in the working directory
file1_2007_07_03.xls Make sure month and day are two digits. When you sort
the files alphabetically the latest file will be the last file. the files
in the master directory will be just the base name file1.xls

The code below finds all the files names in the master directory. Then
searches for the latest file in the working directory with the same base
name. Finally copies the lastest file in the working direcory to the master
directory and overwrites the old file in the master directory.



Sub updatefiles()

Master = "C:\Temp\Master\"
Working = "C:\Temp\Working"

Set fso = CreateObject _
("Scripting.FileSystemObject")

First = True
Do While (1)
If First = True Then
MasterFile = Dir(Master)
First = False
Else
MasterFile = Dir()
End If

If MasterFile = "" Then Exit Do

'strip off extension
MasterFileBase = Left(MasterFile, _
Len(MasterFile) - 4)
Set fs = Application.FileSearch
With fs
.LookIn = Working
.Filename = MasterFileBase & "*.xls"
If .Execute > 0 Then

'get latest filename
LastestFile = .FoundFiles(1)
For i = 2 To .FoundFiles.Count
If StrComp(.FoundFiles(i), _
LastestFile, vbTextCompare) > 0 Then

LastestFile = .FoundFiles(i)
End If
Next i

'copy lastest file to master directory

fso.CopyFile LastestFile, _
Master & MasterFile


Else
MsgBox "File " & MasterFileBase & ".xls" & _
" Not found in Working Directory"
End If
End With
Loop

End Sub

Joel,
Actually, that's what we have.
first directory is a working directory-- multiple directories, one for each
user, there are 7 users, so we have 7 "working" directories.

The Second directory is a master directory-- one directory for all files,
placed there only by one of two people who are responsible for verifying the
data in each workbook, and once verified, the workbooks are placed in the
final master directory (Generally speaking the files are renamed to a more
basic name, half of which is the original workbook's name).

One of my colleagues began giving his file names the dated addons to make it
clear that they were the most recent version. The dates appear to be removed
when placed in the Master directory. It made sense, so I started doing it
too. I.e., sometimes we can get upwards of 7 to 10 working files in the
various working directories because of how quickly things can move around
here.

Workbook A is still in initial set up stages.
At present, I've taken copies of the specific worksheets that I want from
each of the 35 workbooks I mentioned and placed them into WorkbookA. These
would be my "baseline" worksheets.
From this point forward, I'd look in the master directory only for the
specific 35 workbooks. The primary search parameter would be the file name
(ABC-X31... ABC-X66), and the second search parameter would be the most
recent "save" date being newer than the last save date of WorkbookA (my
preference is that it'd look at a save date being newer than the specific
worksheet, but my understanding is that MS does not allow for that. Unless I
could have it look at the footer strings placed there by the user when they
last printed the specific worksheet-- which would in fact work for my
purposes[question is: can that be done?].).

I've been tinkering with the idea I've got to replace worksheets, instead of
just linking the data in the worksheets. The link seemed to be too easily
broken, and could quickly cascade to a major problem.

Since I'm looking through a number of workbooks, it seems I need to start
with a FOR,to,next loop.

Within the for loop, I'd need to have a series of nested IF statements
looking for true/false items.
IF(stringName =
workbookName,(IF(date>LastSaveDate,workbook.open,ignore),ignore)
Where the stringName would be the file name that I'm looking for.
The date would be the appropriate code-- which while I think it does exist,
I'm not familliar with.

Once the workbook is open, I'd want to look for a specific worksheet named
'A' or 'B.' Once I found one of those two, I'd take a copy of the worksheetA,
or worksheetB, and paste it into my existing workbookA.

If my understanding of the language is correct, that'd be worksheet.copy,
and then worksheet.paste.

After the new worksheet is located within workbookA, I'd want to rename it
by looking on the second line of that worksheet for the predetermined
number-- X31.... X66. Once I found that, it'd be placed in the worksheet name
using a rename command.
Then, so that I don't keep "duplicating" worksheets, it'd need to look at
all of the worksheets (in workbookA) to see if an existing worksheet exists.
If it does exist-- and it should-- delete the existing one, and replace it
with the new.

In explaining this to one of my colleagues, he said that he could readily
identify further uses for it with his own workbooks, and independent
directories.
So.... as I think about this.
1- Does this sound correct/feasible?
2- what are some of the commands that I'd need to perform the tasks that I'm
seeking to accomplish?
such as:
most recent Date saved search?
comparison date search?
string search for a group of file names, one file name at a time? (I suppose
that I need to state here that there are some 750 files in the master
directory, and I only want 35 of them (that are located) in the middle of
that directory.)
workbook.open (X31....X66)
worksheet.copy('A' OR 'B')
worksheet.paste('A' OR 'B')
worksheet.delete((old) X31....X66)
worksheet.rename('A' OR 'B' to (new) X31....X66)
While there is a lot more that I'd like this macro to perform, I think that
this is enough for the time being to just get it up and going. Once this part
is working, I can come back for more.
I hope this is better explained this time. If not, please ask.
Thank you, in advance.
Best.

I think I would do things different than your recommendations. I would have
two directories. One a working directory where the users update the files
with the date included. I would have a second master directory that would
have the files without any date which would be the latetest files.

I would then create a workbook that just contained the macros needed to
update the files. This workbook would copy the latest files from the working
directory into the master directory and then open Workbook A. The links
would never have to change in workbook A.

 

[Joel]

the code I sent you get you to the latest file in the working directory.
Instead of copying the file you need to open the file.

Ther are two ways of getting the latest file in a directory. One is to use
the save date. I don't recommend using the date of the file because somebody
could save an older file without making any changes. I always add a date
stamp to my file names to ikeep track of the latest file
(file1_2007_07_04.xls).

To get to the latest file I still recommend the following.
1) Have in the Master directory one copy of each workbook. You can get each
workbook name from this directory.
2) Time stamp each file in the working directory with the date like I
explained before file1_2007_07_04.xls. Then when you sort each directory the
workbooks with the same name are in time order.


Good Luck.

Joel,
Thanks for your response.
It's not my goal to replace workbooks in the master directory with workbooks
from the working directory. There are others above me that are responsible
for that part of our work.
My goal was to have a master workbook-- workbookA, with a specific set
(before they are moved into workbookA, they were named either 'A' or 'B') of
worksheets (already in workbookA, as of yesterday afternoon, with new names
for each worksheet-- 231 through 266).
These specific worksheets are just one of 35 worksheets within the master
directory workbooks.
I am looking to copy worksheets from the master directory workbooks-- I'd
said 35 or so out of the 700+ we have in our master directory--

I want to check for the most recently saved workbooks, compare those dates
with the last save date of workbook A (wbA), and if the workbooks in the
master directory (within the range of 231 to 266-- out of 1 to 750) have been
saved since the last wbA save, I want to copy one specific sheet over to my
wbA.

I will however tinker with your code and see how it works for me. I know of
least two people at work who would use something like this.
We finally got our copy of the new "Excel 2007 VBA programmer's reference"
book yesterday afternoon, so I'll be busy reading for the next few weeks.
Thanks again for your willingness to answer my questions, and request for
help.
Best

I recommend that you name the files in the working directory
file1_2007_07_03.xls Make sure month and day are two digits. When you sort
the files alphabetically the latest file will be the last file. the files
in the master directory will be just the base name file1.xls

The code below finds all the files names in the master directory. Then
searches for the latest file in the working directory with the same base
name. Finally copies the lastest file in the working direcory to the master
directory and overwrites the old file in the master directory.



Sub updatefiles()

Master = "C:\Temp\Master\"
Working = "C:\Temp\Working"

Set fso = CreateObject _
("Scripting.FileSystemObject")

First = True
Do While (1)
If First = True Then
MasterFile = Dir(Master)
First = False
Else
MasterFile = Dir()
End If

If MasterFile = "" Then Exit Do

'strip off extension
MasterFileBase = Left(MasterFile, _
Len(MasterFile) - 4)
Set fs = Application.FileSearch
With fs
.LookIn = Working
.Filename = MasterFileBase & "*.xls"
If .Execute > 0 Then

'get latest filename
LastestFile = .FoundFiles(1)
For i = 2 To .FoundFiles.Count
If StrComp(.FoundFiles(i), _
LastestFile, vbTextCompare) > 0 Then

LastestFile = .FoundFiles(i)
End If
Next i

'copy lastest file to master directory

fso.CopyFile LastestFile, _
Master & MasterFile


Else
MsgBox "File " & MasterFileBase & ".xls" & _
" Not found in Working Directory"
End If
End With
Loop

End Sub

Joel,
Actually, that's what we have.
first directory is a working directory-- multiple directories, one for each
user, there are 7 users, so we have 7 "working" directories.

The Second directory is a master directory-- one directory for all files,
placed there only by one of two people who are responsible for verifying the
data in each workbook, and once verified, the workbooks are placed in the
final master directory (Generally speaking the files are renamed to a more
basic name, half of which is the original workbook's name).

One of my colleagues began giving his file names the dated addons to make it
clear that they were the most recent version. The dates appear to be removed
when placed in the Master directory. It made sense, so I started doing it
too. I.e., sometimes we can get upwards of 7 to 10 working files in the
various working directories because of how quickly things can move around
here.

Workbook A is still in initial set up stages.
At present, I've taken copies of the specific worksheets that I want from
each of the 35 workbooks I mentioned and placed them into WorkbookA. These
would be my "baseline" worksheets.
From this point forward, I'd look in the master directory only for the
specific 35 workbooks. The primary search parameter would be the file name
(ABC-X31... ABC-X66), and the second search parameter would be the most
recent "save" date being newer than the last save date of WorkbookA (my
preference is that it'd look at a save date being newer than the specific
worksheet, but my understanding is that MS does not allow for that. Unless I
could have it look at the footer strings placed there by the user when they
last printed the specific worksheet-- which would in fact work for my
purposes[question is: can that be done?].).

I've been tinkering with the idea I've got to replace worksheets, instead of
just linking the data in the worksheets. The link seemed to be too easily
broken, and could quickly cascade to a major problem.

Since I'm looking through a number of workbooks, it seems I need to start
with a FOR,to,next loop.

Within the for loop, I'd need to have a series of nested IF statements
looking for true/false items.
IF(stringName =
workbookName,(IF(date>LastSaveDate,workbook.open,ignore),ignore)
Where the stringName would be the file name that I'm looking for.
The date would be the appropriate code-- which while I think it does exist,
I'm not familliar with.

Once the workbook is open, I'd want to look for a specific worksheet named
'A' or 'B.' Once I found one of those two, I'd take a copy of the worksheetA,
or worksheetB, and paste it into my existing workbookA.

If my understanding of the language is correct, that'd be worksheet.copy,
and then worksheet.paste.

After the new worksheet is located within workbookA, I'd want to rename it
by looking on the second line of that worksheet for the predetermined
number-- X31.... X66. Once I found that, it'd be placed in the worksheet name
using a rename command.
Then, so that I don't keep "duplicating" worksheets, it'd need to look at
all of the worksheets (in workbookA) to see if an existing worksheet exists.
If it does exist-- and it should-- delete the existing one, and replace it
with the new.

In explaining this to one of my colleagues, he said that he could readily
identify further uses for it with his own workbooks, and independent
directories.
So.... as I think about this.
1- Does this sound correct/feasible?
2- what are some of the commands that I'd need to perform the tasks that I'm
seeking to accomplish?
such as:
most recent Date saved search?
comparison date search?
string search for a group of file names, one file name at a time? (I suppose
that I need to state here that there are some 750 files in the master
directory, and I only want 35 of them (that are located) in the middle of
that directory.)
workbook.open (X31....X66)
worksheet.copy('A' OR 'B')
worksheet.paste('A' OR 'B')
worksheet.delete((old) X31....X66)
worksheet.rename('A' OR 'B' to (new) X31....X66)
While there is a lot more that I'd like this macro to perform, I think that
this is enough for the time being to just get it up and going. Once this part
is working, I can come back for more.
I hope this is better explained this time. If not, please ask.
Thank you, in advance.
Best.

:

I think I would do things different than your recommendations. I would have
two directories. One a working directory where the users update the files
with the date included. I would have a second master directory that would
have the files without any date which would be the latetest files.

I would then create a workbook that just contained the macros needed to
update the files. This workbook would copy the latest files from the working
directory into the master directory and then open Workbook A. The links
would never have to change in workbook A.