File not saving correctly after running macro

[seraaj]

Hi Everyone,

Hope you're well

I have a macro that I am running trying to save an attachment into a specific folder. Its working well except for one part.

'examine each attachment in folder - only saves emails with excel attachments
If SubFolder.Items.Count > 0 Then
For Each Item In SubFolder.Items
For Each Atmt In Item.Attachments
If Right(Atmt.FileName, 3) = "xls" Then

'Here: This is suppose to save the file name with yesterdays date but instead of it saving as "File 20150303.xls" it's saving as
"File.xls20150303.xls"
FileName = "\\Server\Serverfolder\Folder1\Folder2\Folder3\Folder4\March 2015\" & Atmt.FileName & Format(Date - 1, "yyyymmdd") & ".xls"

' The rest in fine. I just thought I'd add this too for more detail
Atmt.SaveAsFile FileName
i = i + 1
End If
Next Atmt
Next Item
'summary measures of actions
If i > 0 Then
MsgBox "I found " & i & " attached files." _
& vbCrLf & "I have saved them into the Folder4\March 2015\ folder" _
& vbCrLf & vbCrLf & "Have a nice day.", vbInformation, "Finished!"


I need it to save as Filename 20150303.xls but I have no idea why it's not doing that.

Please help.

Thank you in advanced

 

[Darren Bartrup]

Hi & welcome to the board.

Try

Filename = "\\Server\Serverfolder\Folder1\Folder2\Folder3\Folder4\March 2015\" & Mid(Atmt.Filename, 1, Len(Atmt.Filename) - 4) & Format(Date - 1, "yyyymmdd") & ".xls"

Will they all be 2003 files, or may some be 2007+ files which have four rather than three letter extensions.

This code may come in handy:

'----------------------------------------------------------------------------------
' Procedure : GetExt
' Purpose   : Returns the extension of a file.
'-----------------------------------------------------------------------------------
Public Function GetExt(FileName As String) As String
    Dim mFSO As Object
    Set mFSO = CreateObject("Scripting.FileSystemObject")
    GetExt = mFSO.GetExtensionName(FileName)
    Set mFSO = Nothing
End Function


Sub test1()


    MsgBox GetExt(ThisWorkbook.FullName)


End Sub

 

[seraaj]

Tell me Darren,

Are you this Awesome all the time

Thanks a million I really appreciate it. These files have been 2003 files for a very long time. I will keep your code for 2007 in a safe place until the time comes.

Really appreciate the feedback.

Kindest Regards,
Seraaj (South Africa)

 

[Darren Bartrup]

Nah, I have my off days.

Happy to help though.

 

[Norie]

If you want to remove any file extension you can use InStrRev.

Left(Atmt.FileName, InStrRev(Atmt.FileName, ".")-1)

 

[Darren Bartrup]

Good point.