How can I find the date modified of an external file in Excel with a
macro? I am trying to merge only worksheets from files that are not
already in the current worksheet. Any ideas how to do this? thanks in
advance!

s = "C:\Myfolder\MySheet.xls"
dt = filedatetime(s)

--
Regards,
Tom Ogilvy


"Koveras" wrote:

> How can I find the date modified of an external file in Excel with a
> macro? I am trying to merge only worksheets from files that are not
> already in the current worksheet. Any ideas how to do this? thanks in
> advance!
>
>

How would I use this with a For Next Loop for all files in a folder?
Would I need to use BuiltinDocumentProperties("last save time") like
for example: If TargetWkbk.BuiltinDocumentProperties("last save time")
> ActiveWorkbook.BuiltinDocumentProperties("last save time") Then and put the For Next loop in here? I dont have a lot of VB knowledge and this is confusing me. Thanks for your help


Tom Ogilvy wrote:
> s = "C:\Myfolder\MySheet.xls"
> dt = filedatetime(s)
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Koveras" wrote:
>
> > How can I find the date modified of an external file in Excel with a
> > macro? I am trying to merge only worksheets from files that are not
> > already in the current worksheet. Any ideas how to do this? thanks in
> > advance!
> >
> >

Sub ABC()
Dim rw as Long, sPath as String
Dim sName as string
rw = 2
sPath = "C:\Myfolder\"
sName = Dir(sPath & "*.xls")
do while sName <> ""
cells(rw,1).Value = sName
cells(rw,2).Value = FileDateTime(sPath & sName)
rw = rw + 1
Loop
End Sub


just for illustration: from the immediate window:
? filedatetime("E:\Data\KZ081-Default Survey1.xls")
3/17/2002 5:51:20 PM


--
Regards,
Tom Ogilvy


"Koveras" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> How would I use this with a For Next Loop for all files in a folder?
> Would I need to use BuiltinDocumentProperties("last save time") like
> for example: If TargetWkbk.BuiltinDocumentProperties("last save time")
>> ActiveWorkbook.BuiltinDocumentProperties("last save time") Then and put
>> the For Next loop in here? I dont have a lot of VB knowledge and this is
>> confusing me. Thanks for your help
>
>
> Tom Ogilvy wrote:
>> s = "C:\Myfolder\MySheet.xls"
>> dt = filedatetime(s)
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Koveras" wrote:
>>
>> > How can I find the date modified of an external file in Excel with a
>> > macro? I am trying to merge only worksheets from files that are not
>> > already in the current worksheet. Any ideas how to do this? thanks in
>> > advance!
>> >
>> >
>

This is excellent. I can definitely use this except it causes an
endless loop and only obtains the first file and writes it in all 65536
rows before crashing. What do I need to add to resolve this? Thanks
so much for the help!

Tom Ogilvy wrote:
> Sub ABC()
> Dim rw as Long, sPath as String
> Dim sName as string
> rw = 2
> sPath = "C:\Myfolder\"
> sName = Dir(sPath & "*.xls")
> do while sName <> ""
> cells(rw,1).Value = sName
> cells(rw,2).Value = FileDateTime(sPath & sName)
> rw = rw + 1
> Loop
> End Sub
>
>
> just for illustration: from the immediate window:
> ? filedatetime("E:\Data\KZ081-Default Survey1.xls")
> 3/17/2002 5:51:20 PM
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Koveras" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > How would I use this with a For Next Loop for all files in a folder?
> > Would I need to use BuiltinDocumentProperties("last save time") like
> > for example: If TargetWkbk.BuiltinDocumentProperties("last save time")
> >> ActiveWorkbook.BuiltinDocumentProperties("last save time") Then and put
> >> the For Next loop in here? I dont have a lot of VB knowledge and this is
> >> confusing me. Thanks for your help
> >
> >
> > Tom Ogilvy wrote:
> >> s = "C:\Myfolder\MySheet.xls"
> >> dt = filedatetime(s)
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "Koveras" wrote:
> >>
> >> > How can I find the date modified of an external file in Excel with a
> >> > macro? I am trying to merge only worksheets from files that are not
> >> > already in the current worksheet. Any ideas how to do this? thanks in
> >> > advance!
> >> >
> >> >
> >

Left out a critical line of code:

Sub ABC()
Dim rw as Long, sPath as String
Dim sName as string
rw = 2
sPath = "C:\Myfolder\"
sName = Dir(sPath & "*.xls")
do while sName <> ""
cells(rw,1).Value = sName
cells(rw,2).Value = FileDateTime(sPath & sName)
rw = rw + 1
sName = Dir()
Loop
End Sub

My apologies.

--
Regards,
Tom Ogilvy


"Koveras" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> This is excellent. I can definitely use this except it causes an
> endless loop and only obtains the first file and writes it in all 65536
> rows before crashing. What do I need to add to resolve this? Thanks
> so much for the help!
>
> Tom Ogilvy wrote:
>> Sub ABC()
>> Dim rw as Long, sPath as String
>> Dim sName as string
>> rw = 2
>> sPath = "C:\Myfolder\"
>> sName = Dir(sPath & "*.xls")
>> do while sName <> ""
>> cells(rw,1).Value = sName
>> cells(rw,2).Value = FileDateTime(sPath & sName)
>> rw = rw + 1
>> Loop
>> End Sub
>>
>>
>> just for illustration: from the immediate window:
>> ? filedatetime("E:\Data\KZ081-Default Survey1.xls")
>> 3/17/2002 5:51:20 PM
>>
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Koveras" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > How would I use this with a For Next Loop for all files in a folder?
>> > Would I need to use BuiltinDocumentProperties("last save time") like
>> > for example: If TargetWkbk.BuiltinDocumentProperties("last save time")
>> >> ActiveWorkbook.BuiltinDocumentProperties("last save time") Then and
>> >> put
>> >> the For Next loop in here? I dont have a lot of VB knowledge and this
>> >> is
>> >> confusing me. Thanks for your help
>> >
>> >
>> > Tom Ogilvy wrote:
>> >> s = "C:\Myfolder\MySheet.xls"
>> >> dt = filedatetime(s)
>> >>
>> >> --
>> >> Regards,
>> >> Tom Ogilvy
>> >>
>> >>
>> >> "Koveras" wrote:
>> >>
>> >> > How can I find the date modified of an external file in Excel with a
>> >> > macro? I am trying to merge only worksheets from files that are not
>> >> > already in the current worksheet. Any ideas how to do this? thanks
>> >> > in
>> >> > advance!
>> >> >
>> >> >
>> >
>