Compare xls & mpp files against a master file register then flag missing.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roger from Auckland - 17 Sep 2007 00:22 GMT

Hi,
Excel 2003
On either a weekly or fortnightly cycle, I have to check against a
master register of file names, files that may or may not exist in two
separate locations on a shared drive, and flag up files that should be
there.
One folder contains xls files, the second folder contains mpp files.
I need to bring the names of these files into a spreadsheet, then
compare against the master file registry.
Can anyone point me to a macro which could show me the right steps to
take?

Regards
Roger

Reply to this Message

Tom Ogilvy - 17 Sep 2007 12:12 GMT

Sub getfilenames()
Dim s as String, rw as Long
Dim fname as String
s = "C:\Myfolder\"

rw = 1
fname = Dir(s & "*.xls")
do while fname <> ""
cells(rw,1) = fname
rw = rw + 1
fname = dir()
Loop
s = "C:\Myfolder1"
rw = 1
fname = Dir(s & "*.mpp")
do while fname <> ""
cells(rw,1) = fname
rw = rw + 1
fname = dir()
Loop
End Sub

Signature

regards,
Tom Ogilvy

> Hi,
> Excel 2003
[quoted text clipped - 10 lines]
> Regards
> Roger

Reply to this Message

Roger - 17 Sep 2007 21:03 GMT

On Sep 17, 11:12 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:

> Sub getfilenames()
> Dim s as String, rw as Long
[quoted text clipped - 36 lines]
> > Regards
> > Roger

Tom, thanks for taking the time out to reply.
I installed the macro and ran it with no results so I'm just checking
that I've not snafooed things.

Sub getfilenames()
Dim s as String, rw as Long
Dim fname as String
s = "C:\Myfolder\"

The s="C:\Myfolder\" I've understood to be the two locations for the
*.xls and *.mpp files, so I copied the appropriate location into the
macro.
Is this all that is required?
What is the expected outcome? i.e. should I get two sets of files,
one set the *.xls, and below that the other set *.mpp?

How do I send the output of the macro to put the *.xls files in say
B10 and the output of the *.mpp files into D10?

Again, thanks for your help...

Incidentally, is there a good primer/PDF on the net explaining in
simple terms an overview of the Macro/VB usage in Excel? The internal
help file is not so user friendly.

Regards
Roger

Reply to this Message

Dave Peterson - 17 Sep 2007 22:47 GMT

The output of Tom's original code would have been placed on the activesheet. So
clean that sheet up before you start (or insert a new worksheet first???).

And you should have changed those two lines "S=...." to point at the correct
folders. Did you include the final backslash?

Option Explicit
Sub getfilenames()
Dim s As String, rw As Long
Dim fname As String
s = "C:\folder1\"

rw = 10
fname = Dir(s & "*.xls")
Do While fname <> ""
Cells(rw, "b") = fname
rw = rw + 1
fname = Dir()
Loop
s = "c:\folder2\"
rw = 10
fname = Dir(s & "*.mpp")
Do While fname <> ""
Cells(rw, "D") = fname
rw = rw + 1
fname = Dir()
Loop
End Sub

There was a minor bug in Tom's first response.

That second "rw = 1" start the output at row 1 (column A) and may have
overwritten any .xls filenames that would have been there.

In the new code, you want to start back at row 10, but in a different column.

If it doesn't work for you, then post the code you used. It'll make finding any
errors easier.

> On Sep 17, 11:12 pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 66 lines]
> Regards
> Roger

Signature

Dave Peterson

Reply to this Message

Roger - 18 Sep 2007 02:16 GMT

> The output of Tom's original code would have been placed on the activesheet. So
> clean that sheet up before you start (or insert a new worksheet first???).
[quoted text clipped - 109 lines]
>
> Dave Peterson

Dave hi,
Problem fixed with the inclusion of the final "\"....
Thanks a mill.
Roger...

Reply to this Message