Extracting attachments and rename file names

[koltregaskes]

We received about 15 reports from various sources each day to our reporting mailbox. I have created Outlook VBA scripts, which are run from a message rule, to save each report from the emails to the relevant network folder (for different projects). I have a few questions on extracting and renaming the attachments:

1. How can I extract a file from an attachment with VBA to a specific folder? Example name is "report_20170831070501.zip"

2. How can I rename the file to remove the last 2 characters? Example name is report_20170831070501.csv - so would like to remove the "01", as in the seconds.

3. How can I rename the file of another report to remove the timestamp at the end of the name but keep the datestamp? Timestamp in hh-mm-ss format, example name of report is "This Report_2017-08-31-05-41-56.xlsx".

I normally use Name "this name", "to this name" to rename files and I guess it's something like left(oFile.name,2) to remove the ends but I can't figure out how to put these together to rename and remove the ends.

If you need any further info from me, just shout.

Ta.

[gmayor]

This request is somewhat confusing. Your message appears to suggest that you receive reports in ZIP format. Do you want to rename the ZIP file, or do you want to extract the files from the zip and rename those? What exactly is in the Zip file? Is it just the one CSV format file, or XLSX file or both?

1 - see http://www.rondebruin.nl/win/s7/win002.htm
2 -
Dim strName As String
Dim strNewName As String: strNewName = ""
Dim strPath As String
Dim strExt As String
Dim strDate As String
strPath = "C:\Path\"
strName = "report_20170831070501.csv"
If InStr(1, strName, "_") > 0 Then
strExt = Right(strName, Len(strName) - InStrRev(strName, Chr(46)) + 1)
strNewName = Left(strName, Len(strName) - Len(strExt) - 2)
strDate = Split(strNewName, "_")(1)
If Len(strDate) = 12 And IsNumeric(strDate) = True Then
strNewName = Left(strNewName, Len(strNewName) - 2) & strExt
End If
Name strPath & strName As strPath & strNewName
End If
3 - You should be able to work out 3 from 2

[koltregaskes]

Yes we receive ZIP files and I would like to extract then rename the files (the ZIP contains an Excel sheet). It is one ZIP per mail, containing one Excel file.

Thanks I'll look at the code.

[koltregaskes]

Graham,

Thanks for the info.

1. Unfortunately the unzip code didn't work, it wouldn't unzip the files, though creating the folder worked. But I eventually found this code that did work [forum won't let me post with the URL unfortunately]

2. Great, that works perfectly.

3. I'm having a problem with one report, the report is called "Report_2017-09-05-05-46-13.xlsx". For strName I can use = "Report_" & Format(Date, "yyyy-mm-dd... for the first part of the file name but the timestamp at the end is different every day. So does anyone know how I find strName with this file name?

[gmayor]

3 is a little more complicated than 2 was, but the principles are the same


Dim strName As String
Dim strNewName As String: strNewName = ""
Dim strPath As String
Dim strExt As String
Dim strDate As String
Dim vNewDate As Variant
strPath = "C:\Path\"
strName = "Report_2017-09-05-05-46-13.xlsx"
If InStr(1, strName, "_") > 0 Then
If UBound(Split(strName, "-")) = 5 Then
strExt = Right(strName, Len(strName) - InStrRev(strName, Chr(46)) + 1)
strNewName = Left(strName, Len(strName) - Len(strExt) - 2)
strDate = Split(strNewName, "_")(1)
vNewDate = Split(strDate, "-")
strNewName = Split(strNewName, "_")(0) & "_" & _
vNewDate(0) & "-" & vNewDate(1) & "-" & _
vNewDate(2) & strExt
MsgBox strNewName
'Name strPath & strName As strPath & strNewName
End If
End If

[koltregaskes]

Thanks for the reply again, Graham,

Still on 3. Just to confirm, I would like to rename an Excel file from "Report_2017-09-05-??-??-??.xlsx" [the questions marks being different every day] to "Report_2017-09-05.xlsx". So for the above code I can't put in strName = "Report_2017-09-05-05-46-13.xlsx" as the timestamp at the end will be unknown.

In case anyone reads this post at a later date, the above code for removing the last 2 characters from a file name needs a tiny tweak as it's actually deleting 4 characters. The code should read (no need to have the second "-2" as that appears to remove 2 more characters after already removing 2):


Dim strName As String
Dim strNewName As String: strNewName = ""
Dim strPath As String
Dim strExt As String
Dim strDate As String
strPath = "C:\Path\"
strName = "report_20170831070501.csv"
If InStr(1, strName, "_") > 0 Then
strExt = Right(strName, Len(strName) - InStrRev(strName, Chr(46)) + 1)
strNewName = Left(strName, Len(strName) - Len(strExt) - 2)
strDate = Split(strNewName, "_")(1)
If Len(strDate) = 12 And IsNumeric(strDate) = True Then
strNewName = Left(strNewName, Len(strNewName)) & strExt
End If
Name strPath & strName As strPath & strNewName
End If

[gmayor]

Did you try the code I posted? strName is the name of your report. I put in the example you posted but it doesn't matter what the time code is.

[koltregaskes]

Hi Graham,

Sorry for my stupidity but I'm still not following. I don't know what the report name is each day like I say. For example, today's report was called "Report_2017-09-06-05-53-04.xlsx", yesterday's was "Report_2017-09-05-05-46-13.xlsx" and the day before was called "Report_2017-09-04-05-39-22.xlsx", so a differ time each day. The only pattern is that it's run at 5am but the minutes and seconds differ every day. So I'm looking for the script to find this report and remove the timestamp (the last 9 characters).

So I could use your first script above and change the "- 2" to "- 9" but the strName = line needs alternating to pick up the ever changing report names. If it wasn't for the timestamp at the end I can do the following to find today's report but need an extra bit for the timestamp or the last 9 characters:

strName = "Report_" & Format(Date, "yyyymmdd") & [need-something-here-to-find-whatever-the-timestamp-will-be]".xlsx"

So I would be looking to automatically rename the following:

Report_2017-09-06-05-53-04.xlsx
Report_2017-09-05-05-46-13.xlsx
Report_2017-09-04-05-39-22.xlsx

to (respectively):

Report_2017-09-06.xlsx
Report_2017-09-05.xlsx
Report_2017-09-04.xlsx

Perhaps one solution could be to rename any report that beings with the filename "Report_" in strPath. In theory there will only ever be one "Report_yyyy-mm-dd-hh-mm-ss" report in that folder.

Ta.

[gmayor]

It doesn't matter what the file is called as long as it follows the same pattern. Instead of
strName = "Report_2017-09-05-05-46-13.xlsx"
Point strName to the extracted filename