How to load these XLS files

[Jako]

I am receiving workbooks from someone who is using the filename a
follows : <TodaysDate> NW AB.XLS

For example today i received a workbook with this filname...

180704 NW AB.xls

and tomorrow i will receive a workbook named

190704 NW AB.xls

Also the received files are stored in C:\CASTLE\NORTH WEST\

Please could someone tell me how to programmatically write some code t
auto load the file received without having to browse for th
file.Obviously it has something to do with Format Date but am unsure f
exactly how to do this. Also before loading the file i want to copy th
received file into D:\RECEIVED\NORTH WEST\

Thanks in advance

 

[Bob Phillips]

Workbooks.Open Filename:= "C:\CASTLE\NORTH WEST\" & _
Format(Date,"ddmmyy") & " NW AB.xls"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Dim s1 as String, s2 as Strnig, sName as String
s1 = "C:\CASTLE\NORTH WEST\"
s2 = "D:\RECEIVED\NORTH WEST\"
sName = format(Date,"ddmmyy") & " NW AB.xls"
filecopy s1 & sName, s2 & sName
workbooks.open s1 & sName

 

[Jako]

Many thanks that works a treat.

Could you please tell me how i delete the file from

"C:\CASTLE\NORTH WEST\"

after copying it.

Also i don't want to be prompted if i want to delete the file.

Cheer

 

[Tim Coddington]

Name is actually a move, not a copy. I'm sure you could do the
name command, then open the sheet on the D: drive to use it. A
little modification of Tom's code should do the trick.

But, for future reference, the keyword to look up in help is KILL.

 

[Tim Coddington]

Oops. Sorry. Name doesn't work across devices. I guess you do need the
KILL after all.

Name is actually a move, not a copy. I'm sure you could do the
name command, then open the sheet on the D: drive to use it. A
little modification of Tom's code should do the trick.

But, for future reference, the keyword to look up in help is KILL.