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

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

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

On Sep 17, 11:12 pm, Tom Ogilvy
wrote:
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

--
regards,
Tom Ogilvy

"Roger from Auckland" wrote:
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

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

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

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

On Sep 17, 11:12 pm, Tom Ogilvy
wrote:
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

--
regards,
Tom Ogilvy

"Roger from Auckland" wrote:
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

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

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

On Sep 18, 9:47 am, Dave Peterson wrote:
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 wrote:

On Sep 17, 11:12 pm, Tom Ogilvy
wrote:
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

--
regards,
Tom Ogilvy

"Roger from Auckland" wrote:
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

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

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