Rename multiple files (add datestamp)?

[jp romano]

Hello,
Working on a project using both MS Excel, Outlook, and Access as seamlessly as possible. Problem is, I'm not a programmer. I understand the basics and have put together some code, but it's not particularly elegant.

Anyway, a part of my project will scan through an outlook folder looking for messages with an attachment. The attachments are .zip files which contain two excel files (which will be imported to a database). The zip files are placed out on a network drive and a date/timestamp added to the filename.

The problem is, the two excel files INSIDE the zipped file have names like
abcMMDDYY.xls. So, we can get 2-4 files with the same name every day.

Do any of you have any idea how I can sweep through the repository and add a date/timestamp to all of the files on the drive?

Thanks for any help! :banghead:

[Kenneth Hobs]

Welcome to the forum!

That is a big project for your first post. What is the repository? Do you mean a folder? Going through a whole drive would take some time. Yes, we could rename and redate them but maybe this approach would be better. Save the new files with an added part. That way, all the files of the same type would be lumped together. The file dates would not matter.

The kb has an article that lets you save filenames given a base name. e.g. test.xls, test-1.xls, test-2.xls, etc. I submitted a kb article that saves fileslike test.xls, test (1).xls, test (2).xls, etc. I have not had time to resubmit it with the recommended changes since the forum moved to the new site. I have to redo the entry and submit it again.

[BrianMH]

I have made an attachment saver at work that saves attachments like test.xls, test(1).xls etc. It allows you to select files in the inbox and download all the attachments from all those mails and in order to avoid overwriting them it uses that method. I can't get it now but I will post it tomorrow if someone hasn't already posted a solution for you. I think you may find it helpful. Actually anyone needing to download hundreds to thousands of attachments regularly (like me) would find it useful.

[jp romano]

Thanks for the speedy reply, Mr Hobs.
You're right about the repository vs folder - all the uniquely named zipped files are in a single folder, and in each are two xls files.
I think I know what you mean by adding a new part, but am not sure how to go about doing that from within the winzip extraction process. Not sure if it'll help, but here's some of the code...

a = the full path and filename which I read from the network and dump into the variable

Source = a

If InStr(1, Source, " ", vbTextCompare) <> 0 Then Source = Chr(34) & Source & Chr(34)
Dest = "C:\OUTLOOKTEMP\unzipped\"
If Dir(Dest, vbDirectory) = "" Then MkDir Dest
If InStr(1, Dest, " ", vbTextCompare) <> 0 Then Dest = Chr(34) & Dest & Chr(34)
ZipIt = Shell _
(WinZipPath & "Winzip32.exe -min -e " & Source & " " & Dest, vbNormalFocus)



I lifted most of this from various sites and modified it a bit to suit my needs. I'm VERY novice!

Thank you again for your response!

[jp romano]

Thanks for the speedy reply!
You're right about the directory vs folder thing. I have several uniquely named zip files in a single folder. Each zip has two or more xls files.

The problem with adding the additional part is that I'm not sure how to do it while using winzip's command line. Not sure if it'll help or make any sense, but here's a bit of the code.

a = the full path and filename of the zip file before the extraction


Source = a
If InStr(1, Source, " ", vbTextCompare) <> 0 Then Source = Chr(34) & Source & Chr(34)
Dest = "C:\OUTLOOKTEMP\unzipped\"
If Dir(Dest, vbDirectory) = "" Then MkDir Dest
If InStr(1, Dest, " ", vbTextCompare) <> 0 Then Dest = Chr(34) & Dest & Chr(34)
ZipIt = Shell _
(WinZipPath & "Winzip32.exe -min -e " & Source & " " & Dest, vbNormalFocus)


Thanks again for any help!

By the way, if you know of a better way to scan outlook emails for attachments, move them over to a network server and unzip them, I'm WIDE open for any suggestions! I'm really a novice at this!

[jp romano]

Thanks...that would be fantastic. Would love to have a look at it. Will be out of the office tomorrow, but will try to head in this weekend to check it out.
THANKS!

[BrianMH]

Option Explicit

Private Sub SaveAttachments_Click()
Dim strSavePath, strPrefix As String
MsgBox ("Open Outlook and select mails you want to download attachments from")

strSavePath = svpth

If strSavePath = "" Then
MsgBox "No folder was selected"
Exit Sub
End If
Call Downloadattachments(strSavePath)

End Sub

Function svpth()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
Dim strSelectedItem As String
With fd

.Title = "please choose save path"

If .Show = -1 Then
svpth = .SelectedItems.Item(1)
Else
End If

End With

End Function
Function Downloadattachments(strPath)
Dim myOlApp As New Outlook.Application
Dim myOlExp As Outlook.Explorer
Dim myOlSel As Outlook.Selection
Dim myOLattachments As Outlook.Attachments
Dim MsgTxt, strPre, strExt As String
Dim w, x, y, z, intExtlen As Integer
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")

Dim recieveddate
Dim strFileName, strNewName As String
Set myOlExp = myOlApp.ActiveExplorer
Set myOlSel = myOlExp.Selection
z = 0
w = 0
For x = 1 To myOlSel.Count
Set myOLattachments = myOlSel.Item(x).Attachments
If myOLattachments.Count <> 0 Then
For y = 1 To myOLattachments.Count
strFileName = myOLattachments(y).DisplayName
If fs.fileexists(strPath & "\" & strFileName) = True Then
strNewName = strFileName
intExtlen = Len(strFileName) - InStrRev(strFileName, ".") + 1
strPre = Left(strFileName, Len(strFileName) - intExtlen)
strExt = Right(strFileName, intExtlen)
While fs.fileexists(strPath & "\" & strNewName) = True
w = w + 1
strNewName = strPre & Chr(40) & w & Chr(41) & strExt
Wend
strFileName = strNewName
w = 0
End If
myOLattachments(y).SaveAsFile strPath & "\" & strFileName

z = z + 1
Next y
End If
skipattach:
myOlSel(x).UnRead = False

Next x
MsgBox ("downloaded " & z & " attachments from " & myOlSel.Count & " emails")
End Function



Heres my attachment saver. It won't unzip the files for you but hopefully it gives you an idea of a way to rename files with the (1), (2) ETC.

[jp romano]

Great stuff...thanks! Instead of using the 1, 2, 3 tails, I'm using the date/timestamp. Working like a charm.
I've still got to get the files INSIDE the zipped files renamed when they're extracted, but now I'm well on my way. Appreciate it!