How to open file with format date and time in excel vba

Question

I want to open and copy sheet in file TFM_20150224_084502 and this file has different date and time each day. I have developed code until open the date format but I can't develop to open it with time format.

What's the more code for it?

Sub OpenCopy ()

Dim directory As String, fileName As String, sheet As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "z:\FY1415\FI\Weekly Report\Astry"
fileName = "TFM_" & Format(Date, "yyyymmdd") & ".xls"

Workbooks.Open "z:\FY1415\FI\Weekly Report\Astry\" & "TFM_" & Format(Date, "yyyymmdd") & ".xls"
Sheets("MSP").Copy After:=Workbooks("Generate Report 2.xlsm").Sheets("PlanOEE")
ActiveSheet.Name = "MSP"

End sub

@JLILIAman There's an example of the filename at the beginning of the post (TFM_20150224_084502) — eirikdaude, Feb 24 '15 at 07:43
Please accept the recent edit of your question so that your code gets properly formatted for other users of SO to read. Thank you very much. — Amen Jlili, Feb 24 '15 at 07:45

score 1 · Answer 1 · edited May 23 '17 at 12:20

It seems that some linebreaks have disappeared when you posted the code into your post, but assuming you are aware of this, I assume that the main problem you have is figuring out the name of the file you want to open?

The VBA Dir-function lets you search for a file in a folder, and lets you include wildcards in your search. I've included this function in your sub, and have tested it with a similarly named file on my computer (albeit without the copying of the sheet), and it opened the sheet:

Sub OpenCopy()

  Dim directory As String, fileName As String, sheet As Worksheet

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  directory = "z:\FY1415\FI\Weekly Report\Astry\"
  fileName = Dir(directory & "TFM_" & Format(Date, "yyyymmdd") & "*.xls*")

  If fileName <> "" Then
    With Workbooks.Open(directory & fileName)
      .Sheets("MSP").Copy After:=Workbooks("Generate Report 2.xlsm").Sheets("PlanOEE")
    End With
    ActiveSheet.Name = "MSP"
  End If

  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

The line relevant for finding the filename is, as you probably see:

fileName = Dir(directory & "TFM_" & Format(Date, "yyyymmdd") & "*.xls*")

I have simply used Dir to do a search for file fitting the string inside the parantheses, where the asterisks are wildcards. The reason I have included an asterisk after xls too is because there is a chance the file can have extensions such as xlsx or xlsm in newer versions of office. I've also added a backslash at the end of the directory string, since you'll have to include it before the filename anyway.

I have also added an if-clause around what you do with the workbook you open, in case no file fitting the search is found.

Note that this sub will only do what you want provided that there only is one file generated for each date. If you want to loop through all files which includes a given date, I would recommend having a look at this post here on SO, which explains how to loop through all files in a folder, modifying the macros presented there to fit your needs should be fairly trivial.

How to open file with format date and time in excel vba

1 Answers1