Date format to m-dd-yy

[Rfcolon428]

I'm trying to open a file with a date in it. I get a runtime error 1004. It can't find the file name with the date of 2/8/2019. I want the date to be in the m-dd-yy format, that is included in the name of the file.

Sub inputboxtest()

Dim dateNeeded As Date
dateNeeded = Format(Date, "m-dd-yy")


dateNeeded = inputbox("what prior date do you need?")
'Enter date as m-dd-yy. No zero in front of m.



Workbooks.Open ("C:\Users\rc\Desktop\All PO Report as of " & dateNeeded & ".xlsm")


End Sub

 

[KennyGreens]

Hi, I would assume it is because a file name cannot have the "/" character in it.

 

[Rfcolon428]

correct.

 

[KennyGreens]

So what exactly are you asking then?

 

[shg]

Perhaps ...

Sub inputboxtest()
  Const sPath       As String = "C:\Users\rc\Desktop\All PO Report as of "
  Dim sFile         As String
  Dim sDate         As String

  sDate = InputBox("Date?")
  If Len(sDate) = 0 Then Exit Sub

  On Error Resume Next
  sDate = Format(CDate(sDate), "m-dd-yy")
  
  If Error.Number Then
    MsgBox "Invalid date!"
  
  Else
    sFile = sPath & sDate * ".xlsm"
    If Len(Dir(sFile)) Then
      Workbooks.Open sFile
    Else
      MsgBox "File not found: " & sFile
    End If
  End If
End Sub

 

[Joe4]

Note that dates are actually stored in Excel as numbers, specifically the number of days since 1/0/1900.
Hence, date fields cannot have slashes in them (that is just formatting when preseting the the date).

Also, the FORMAT function returns a STRING value, not DATE.

Try something like this:

[COLOR=#333333]Sub inputboxtest()[/COLOR]

[COLOR=#333333]Dim dateNeeded As Date[/COLOR]
[COLOR=#333333]Dim dateNeeded2 as String[/COLOR]


[COLOR=#333333]dateNeeded = inputbox("what prior date do you need?")[/COLOR]
[COLOR=#333333]'Enter date as m-dd-yy. No zero in front of m.[/COLOR]

dateNeeded2 = [COLOR=#333333]Format(dateNeeded, "m-dd-yy")[/COLOR]

[COLOR=#333333]Workbooks.Open ("C:\Users\rc\Desktop\All PO Report as of " & dateNeeded2 & ".xlsm")[/COLOR]


[COLOR=#333333]End Sub[/COLOR]

 

[mole999]

excel dates are just Integers, how you format that whole number for data retrival would be down to you. when you test it with F8 what does hovering over the value look like if you stop on workbooks open.

maybe also look to drop the format down after the data entry

 

[Rfcolon428]

This works great. Thank you!