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

[Roger from Auckland]

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

 

[Guest]

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

 

[Roger]

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

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

 

[Dave Peterson]

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.

 

[Roger]

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.

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