Automate date in VBA code

[pam53154]

I want to write a macro which opens an Excel workbook which has the current month-end date in the file name (other than the date, the file name is the same each month). Right now I open the VBA code each month and manually type in the current month-end date so that the correct file opens. Is there a way to have the date update automatically?

 

[Desu Nota from Columbus]

I want to write a macro which opens an Excel workbook which has the current month-end date in the file name (other than the date, the file name is the same each month). Right now I open the VBA code each month and manually type in the current month-end date so that the correct file opens. Is there a way to have the date update automatically?

I would achieve this by having the macro add the formula =Now() in some dummy cell and then tie in the file name to the date from this cell.

Can you post your code?

 

[pam53154]

I can't post the code because it has a lot of company-specific information related to my employer embedded in it. Opening the file is just the first step in the code - then I want to pull certain information from the file, so the formulas I am using to pull information are also in the code and therefore also rely on the date in the filename being updated each month.

 

[Jonmo1]

Can you post just the one line that opens the book?
changing the filename to protect identity?


You can use the Format(Date,"mmmm") function and concatenate month name into the filename..

Workbooks.Open Filename:="C:\blah\blahblah\blah\Filename" & Format(Date,"mmmm") & ".xls"

that would translate into

Workbooks.Open Filename:="C:\blah\blahblah\blah\FilenameJune.xls"

 

[Desu Nota from Columbus]

I can't post the code because it has a lot of company-specific information related to my employer embedded in it. Opening the file is just the first step in the code - then I want to pull certain information from the file, so the formulas I am using to pull information are also in the code and therefore also rely on the date in the filename being updated each month.

Instead of now(), I would suggest using =today().

Dim fileDate As Date

Range("AA10000").Value = "=TODAY()"
fileDate = Range("AA10000").Value

Hopefully you use this and maybe concatenate this to the filename you want to open.

If you need to format the date to match the filename you can do this:

Format(fileDate, "dd-mm-yyyy")

hope this helps

 

[John_w]

Try something like this. Change the folder path and file name etc. to suit and the date format to the required format.

    'Date of last day of current month
    
    Dim lastDayDate As String
    
    lastDayDate = Format(DateSerial(Year(Date), Month(Date) + 1, 1) - 1, "dd-mm-yyyy")
    Workbooks.Open Filename:="C:\Folder\To\Your\Workbook_" & lastDayDate & ".xls"

 

[pam53154]

This is what I have:

Workbooks.Open Filename:= _
"W:\xxx\xxx\xxx\2011.05.31\20110531 xxx.xlsm"

I would also want the macro to open (in this example) the May file, even if I executed the macro in (for example) October. So, embedding a current date type of formula into the code probably wouldn't work.

 

[Jonmo1]

I would also want the macro to open (in this example) the May file, even if I executed the macro in (for example) October. So, embedding a current date type of formula into the code probably wouldn't work.

How is the code supposed to know which month's file you want it to open? It can't read your mind...

Can you put in a cell which month you want?

You can use GetOpenFilename, this will prompt you with a window to browse to and select the file you want to open...


Dim MyFile As String
MyFile = Application.GetOpenFileName
Workbooks.Open Filename:= MyFile

 

[pam53154]

Instead of now(), I would suggest using =today().

Dim fileDate As Date

Range("AA10000").Value = "=TODAY()"
fileDate = Range("AA10000").Value

Hopefully you use this and maybe concatenate this to the filename you want to open.

If you need to format the date to match the filename you can do this:

Format(fileDate, "dd-mm-yyyy")

hope this helps

I have the macro working now to open the file. Now, I want to use vlookup to pull information from the file. So, I need to insert the date variable there as well. Here is what I have-how would I incorporate the date?

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[1],'[20110531 xxx.xlsm]<tab name>'!C1:C10,MATCH(""xxx"",'[20110531 xxx.xlsm]<tab name>'!R5,0),FALSE)"

 

[Desu Nota from Columbus]

I have the macro working now to open the file. Now, I want to use vlookup to pull information from the file. So, I need to insert the date variable there as well. Here is what I have-how would I incorporate the date?

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[1],'[20110531 xxx.xlsm]<tab name="">'!C1:C10,MATCH(""xxx"",'[20110531 xxx.xlsm]<tab name="">'!R5,0),FALSE)"

If you used my approach which uses a dummy cell (AA10000) where you can control the date before you run the macro, you merely have to reference this dummy cell as the value in the vlookup. I'm assuming the date used in the filename is the same date you wish to lookup...so you can return this date by using the defined term 'fileDate' or Range("AA10000").Value (concatenate as necessary)


</tab></tab>