Macro to re-name a file to yesterday date in a specfic directory

[kbriaz]

Hi Mate,

Excel noob here.

I am trying that my macro to re-name a file which is on my desktop to yesterday's date i.e 'File 4-8-13'

Macro which i record doesn't record this action. How can i make this happen ?

Please Login or Register  to view this content.

I want macro to re-name this 'file.xlsx' to yesterdays date. i.e 'File 4-8-13.xlsx' - If possible - ta

Sorry for the trouble guys, thanks in advance

[oeldere]

I don't think i can help you enough with the solution of your question.

But what's underbeneath this question.

It's just one file, so why not change it manualy?

[kbriaz]

I don't think i can help you enough with the solution of your question.

But what's underbeneath this question.

It's just one file, so why not change it manualy?

Thanks for the reply.

I have to run a file every day which pulls data from a SQL server, sorts it then it needs to be saved.

For audit purposed i still require yesterdays file however changed to yesterdays date.

If this is not possible in excel then its ok. I thought i ask.

Ta

[oeldere]

Maybe you can save the file of today, with todays date (e.g. with an macro) and save the file.

Then tomorrow, today will be yesterday.

[kbriaz]

'Maybe you can save the file of today, with todays date (e.g. with an macro) and save the file'

Excellent reply, however above is not possible as this file is used with a number of Vlookups & other formulas ( in a lot of different other workbooks which i don't have full access to) hence can't be saved under today's date.

So is this not possible in excel ? no code which re-names an exisiting file ?

sorry for the bother.

[oeldere]

I think it can be done (maybe not by me).

But again probably it is the wrong approach.

What's differant in the todays file compared with yesterday's file.

You don't work with an template?

[kbriaz]

Well, its for a project which changes all the time. i mean the data.

New records get added everyday, old ones deleted. amended. etc etc

Its easier to use the orignal file & then use the raw data to drive reports etc.

[oeldere]

Then again.

What's wrong with save it with today's date?

[kbriaz]

todays file can't have any date as lot of other files pull information from it.

[shyamhappy]

I hope below code will help you in saving the file.

Dim wk as WorkBook
Set wk = WorkBook.Open("C:\Documents and Settings\Shyam\Desktop\File.xlsx")
wk.SaveAs("C:\Backup\File " & Format(Now() - 1, "dd - mm - yyyy") & " .xlsx")
wk.Close(True)

[kbriaz]

@ Shuamhappy - Many thanks for the response, i was away.

Just tried your code & it debugs ! is it me ? Sorry for the trouble

Please Login or Register  to view this content.