VBA commands

[Love2Learn]

I'm chasing a VBA string that will open a file which is renamed every day.
Files are identical but are renamed with "todays date". e.g. 'document
28-01-09'.
OR
Is there a formular which will lookup/reference this file being renamed
daily. Cell location of data is identical in each file.

My objective is to overwrite data in an existing "Temp" file which links to
a ppt. daily presentation.

Thankyou

 

[JMB]

You could try VBA
Workbooks.Open Filename:="I:\Excel\Document " & Format(Date, "dd-mm-yy") &
".xls"

you could try building a link in one of your worksheets using
=INDIRECT("'I:\Excel\[Document "&TEXT(TODAY(),"dd-mm-yy")&".xls]Sheet1'!$A$1")

but excel's native indirect function does not work on closed workbooks. You
could check out Laurent Longre's Indirect.ext function included with the
Morefunc addin

http://xcell05.free.fr/morefunc/english/index.htm

 

[Fred Smith]

On suggestion I would make is to use yy-mm-dd as the date format. Then your
file names will sort in chronological order.

Regards,
Fred.

You could try VBA
Workbooks.Open Filename:="I:\Excel\Document " & Format(Date, "dd-mm-yy") &
".xls"

you could try building a link in one of your worksheets using
=INDIRECT("'I:\Excel\[Document
"&TEXT(TODAY(),"dd-mm-yy")&".xls]Sheet1'!$A$1")

but excel's native indirect function does not work on closed workbooks.
You
could check out Laurent Longre's Indirect.ext function included with the
Morefunc addin

http://xcell05.free.fr/morefunc/english/index.htm

I'm chasing a VBA string that will open a file which is renamed every
day.
Files are identical but are renamed with "todays date". e.g. 'document
28-01-09'.
OR
Is there a formular which will lookup/reference this file being renamed
daily. Cell location of data is identical in each file.

My objective is to overwrite data in an existing "Temp" file which links
to
a ppt. daily presentation.

Thankyou

 

[Love2Learn]

Yes, thanks Fred. I'm aware of the yymmdd but my primitive collegues are a
bit slow on the up-take. To make things worse they store all these files in
monthly folders! I've got my work gut out.

Ta.

On suggestion I would make is to use yy-mm-dd as the date format. Then your
file names will sort in chronological order.

Regards,
Fred.

You could try VBA
Workbooks.Open Filename:="I:\Excel\Document " & Format(Date, "dd-mm-yy") &
".xls"

you could try building a link in one of your worksheets using
=INDIRECT("'I:\Excel\[Document
"&TEXT(TODAY(),"dd-mm-yy")&".xls]Sheet1'!$A$1")

but excel's native indirect function does not work on closed workbooks.
You
could check out Laurent Longre's Indirect.ext function included with the
Morefunc addin

http://xcell05.free.fr/morefunc/english/index.htm

I'm chasing a VBA string that will open a file which is renamed every
day.
Files are identical but are renamed with "todays date". e.g. 'document
28-01-09'.
OR
Is there a formular which will lookup/reference this file being renamed
daily. Cell location of data is identical in each file.

My objective is to overwrite data in an existing "Temp" file which links
to
a ppt. daily presentation.

Thankyou

 

[Love2Learn]

I think I'm getting in over my head, but I'll play around with your tips.
Thanks very much.

You could try VBA
Workbooks.Open Filename:="I:\Excel\Document " & Format(Date, "dd-mm-yy") &
".xls"

you could try building a link in one of your worksheets using
=INDIRECT("'I:\Excel\[Document "&TEXT(TODAY(),"dd-mm-yy")&".xls]Sheet1'!$A$1")

but excel's native indirect function does not work on closed workbooks. You
could check out Laurent Longre's Indirect.ext function included with the
Morefunc addin

http://xcell05.free.fr/morefunc/english/index.htm

I'm chasing a VBA string that will open a file which is renamed every day.
Files are identical but are renamed with "todays date". e.g. 'document
28-01-09'.
OR
Is there a formular which will lookup/reference this file being renamed
daily. Cell location of data is identical in each file.

My objective is to overwrite data in an existing "Temp" file which links to
a ppt. daily presentation.

Thankyou