On Sep 18, 9:47 am, Dave Peterson <peter...@verizonXSPAM.net> 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 <TomOgi...@discussions.microsoft.com>
> > 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...
|
Similar Threads
