Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to compare totals and flag those less than or more than | Excel Discussion (Misc queries) | |||
collate rows from several files into one new master file | Excel Discussion (Misc queries) | |||
I have 1 master excel file & want to rec. data from 7 other files | Excel Worksheet Functions | |||
VBA Challenge - Flag Missing Info | Excel Programming | |||
VBA Challenge - Flag Missing Info | Excel Programming |