VBA file rename with a date issue

[M.Enders]

Hello all,

I am writing a simple code to allow me to move files and automatically rename them using VBA.

Currently I am using a date drop down via an ActiveX control, Microsoft Date and time Picker 6.0 (SP6)



I keep getting a run-time error of, File not found.
I can only assume this is because the control is showing the date as 2017/06/28 and the slashes are preventing the file from being created/moved.

does anyone know how I can solve this issue

The coding is:

Private Sub Command145_Click()

Dim FSO As New FileSystemObject
Dim FolderPath1 As String
Dim FolderPath2 As String
Dim Date2 As String

If FSO.FileExists(Nz(Me.Text2.Value, "")) Then
FolderPath1 = Me.Text2.Value
Date2 = Me.DTPicker9
FolderPath2 = "C:\COPA\Soldier_Records" & Me.Combo131 & "" & Me.Combo134 & "_" & Date2 & ".pdf"

Name FolderPath1 As FolderPath2

MsgBox "File Uploaded"

End If


End Sub

Thank you for any help.

Marty

[JoeM]

Try this change:

Date2 = Format(Me.DTPicker9,"yyyymmdd")

[M.Enders]

Well JoeM,

Thank you so much

I feel rather silly that such a simple change corrected the issue, the code that worked is:

Private Sub Command145_Click()

Dim FSO As New FileSystemObject
Dim FolderPath1 As String
Dim FolderPath2 As String
Dim Date2 As String

If FSO.FileExists(Nz(Me.Text2.Value, "")) Then
FolderPath1 = Me.Text2.Value
Date2 = Format(Me.DTPicker9, "yyyymmdd")
FolderPath2 = "C:\COPA\Soldier_Records" & Me.Combo131 & "" & Me.Combo134 & "_" & Date2 & ".pdf"

Name FolderPath1 As FolderPath2

MsgBox "File Uploaded"

End If


End Sub

[JoeM]

You are welcome. If you want to separate the month, day, year, you could use underscores, i.e.

Date2 = Format(Me.DTPicker9, "yyyy_mm_dd")