Vba code to open file which has current date in its name

[aman2059]

Hi All,

I am working on one vba coding and I am stucked in between and wanted help.

Through the macro, I want one file to be opened from the given location and the name of the file is in below format.

"LearnersinMandateDataExport_3657_11302015.xlsx"

In the above format only date changes every time and it would always be current date. for example - if I am running macro today. It would always contain the above name with the current date (mmddyyy). given as below for today

"LearnersinMandateDataExport_3657_01192016.xlsx"

I tried somewhat below.

"Workbooks.Open Filename:= _
"C:\Raw Data\" & Format("LearnersinMandateDataExport_3657_""mmddyyyy"")"

Thank you in advance for help.

 

[BarryL]

"Workbooks.Open Filename:= _
"C:\Raw Data\LearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy"") & ".xlsx"

 

[aman2059]

"Workbooks.Open Filename:= _
"C:\Raw Data\LearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy"") & ".xlsx"

It worked. Thank you so much.

 

[aman2059]

I wanted to check one more thing. This is just an extension to my post.

What would be the code for, if the file is open and then I need to go from one open workbook to another open workbook which has the same name as below

Filename:= _
"EYLearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx

 

[BarryL]

Hi BarryL,

You just replied my post on how to open file with current date in name.

I wanted to check one more thing. This is just an extension to my post.

What would be the code for, if the file is open and then I need to go from one workbook to another workbook which has the same name as below

Filename:= _
"EYLearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx"

just a request if you can help me on this as well.

Thank you once again.

hey, no problem at all.

so in order to flick between workbooks easier you'll need to set them to a variable like below.

dim wb as workbook

set wb = ThisWorkbook

Workbooks.Open Filename:= _
    "EYLearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx"

dim wb2 as workbook

set wb2 = ActiveWorkbook

this way lets say you copied cell A1 from the opened workbook and wanted to paste it in the workbook from which your macro is called from (where the code is written) you could just do the below.

dim wb as workbook

set wb = ThisWorkbook

Workbooks.Open Filename:= _
    "EYLearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx"

dim wb2 as workbook

set wb2 = ActiveWorkbook

wb2.sheets(1).range("A1").copy

wb.range("A1").pastespecial xlpastevalues

application.cutcopymode = false
wb2.close ' this is where setting a workbook makes writing code much shorter

alternatively you can set worksheets to variable e.g

dim wks as worksheet

set wks = thisworkbook.sheets(1)

etc.

make sense? your mailbox is full btw

 

[BarryL]

Option Explicit

Sub aman()
Dim wb As Workbook
Dim wb2 As Workbook
Dim wks As Worksheet
Dim wks2 As Worksheet


Set wb = ThisWorkbook
Set wks = wb.Sheets(SheetName Or IndexNumber)

Workbooks.Open Filename:="C:/"

Set wb2 = ActiveWorkbook
Set wks2 = wb2.Sheets(Name Or Number)

'code for wb2 (copy, filter etc)
'possible link with wb (paste etc)

wb2.Close ' or saveas etc

wb.Activate


End Sub

example of defining variables and using them to flick between

 

[aman2059]

Thanks

I got your point. but I still face problem in one part that is activating the other open workbook. let say macro has to go from book1.xlsx to "EYLearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx"

below code doesn't work to activate the below workbook.

Workbooks.Open Filename:= _
"EYLearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx"

Option Explicit

Sub aman()
Dim wb As Workbook
Dim wb2 As Workbook
Dim wks As Worksheet
Dim wks2 As Worksheet


Set wb = ThisWorkbook
Set wks = wb.Sheets(SheetName Or IndexNumber)

Workbooks.Open Filename:="C:/"

Set wb2 = ActiveWorkbook
Set wks2 = wb2.Sheets(Name Or Number)

'code for wb2 (copy, filter etc)
'possible link with wb (paste etc)

wb2.Close ' or saveas etc

wb.Activate


End Sub

example of defining variables and using them to flick between

 

[BarryL]

post all your code with an explanation as to what it does and ill understand what you mean.

 

[aman2059]

Hi BarryL,

below is the code.

Sub copytonewjoiner()


    Dim FName As String
    Dim FPath As String
    Dim wb1 As ThisWorkbook
    Dim wb2 As Workbook
    
    FPath = "C:\Users\aman.bhardwaj\Desktop\Ideas\Learning process\Master files\testing\saved report"
    FName = "LearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx"

    ' i am unable to open below workbook
    Workbooks.Open ("FPath & " \ " & FName")
      
    Workbooks.Open ("C:\Users\aman.bhardwaj\Desktop\Ideas\Learning process\Master files\testing\New Joiners_Exceptions Report.xlsx")
    
    Windows("LearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx").Activate
    Sheets("Sheet1").Select
    
    Range("T1").Select
    ActiveSheet.Range(("A1"), Selection.End(xlDown)).AutoFilter Field:=20, Criteria1:="", Operator:=xlAnd
    
    
End Sub

Everything worked fine in my code. Only problem I am facing is in opening the workbook with date. For that I wrote below code. I hope it is clear to you.

Workbooks.Open ("FPath & " \ " & FName")

Please suggest how can I open the workbook("LearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx") from the location given in the code.

 

[BarryL]

Sub copytonewjoiner()


    Dim FName As String
    Dim FPath As String
    Dim wb1 As ThisWorkbook
    Dim wb2 As Workbook
    
    FPath = "C:\Users\aman.bhardwaj\Desktop\Ideas\Learning process\Master files\testing\saved report\"
    FName = "LearnersinMandateDataExport_3657_" & Format(Date, "mmddyyyy") & ".xlsx"

    ' i am unable to open below workbook
    Workbooks.Open Filename:=FPath & FName
      Set wb1 = ActiveWorkbook
    Workbooks.Open ("C:\Users\aman.bhardwaj\Desktop\Ideas\Learning process\Master files\testing\New Joiners_Exceptions Report.xlsx")
    Set wb2 = ActiveWorkbook
wb1.Sheets("Sheet1").Select
    
    Range("T1").Select
    ActiveSheet.Range(("A1"), Selection.End(xlDown)).AutoFilter Field:=20, Criteria1:="", Operator:=xlAnd
    
    
End Sub