Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save files as different names
Hello all. I have a procedure that copies excel attachments from an
Outloook folder and pastes them to a predefined drive and folder. Unfortunately, it only works if each file has a different name (I think if they are the same name, it overwrites the original). Of course, I have timesheets coming in via e-mail that all have the same file name. Is there a way to add a counter to the end of each name so all the files are copied in? Thanks! Here's the code I have: Sub SaveAtt() 'Saves attachments to a specified folder Dim ol As Outlook.Application Dim ns As NameSpace Dim Fldr As MAPIFolder Dim Mi As MailItem Dim Att As Attachment Set ol = New Outlook.Application Set ns = ol.GetNamespace("MAPI") Set Fldr = ns.Folders("Public Folders").Folders("All Public Folders").Folders("Timesheet") For Each Mi In Fldr.Items If Mi.Attachments.Count 0 Then For Each Att In Mi.Attachments Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename Next Att End If Next Mi Set Att = Nothing Set Mi = Nothing Set Fldr = Nothing Set ns = Nothing Set ol = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save files as different names
probably easier to rename the existing files
sName = "attachmentname.xls" sName1 = left(sName,len(sName)-4) sPath = "C:\MyTimesheetsFolder\" if dir(sPath & sName) < "" then name sPath & sName as sPath & sName1 & _ datepart("ww",date) - 1 & ".xls" End if -- Regards, Tom Ogilvy "Steph" wrote in message ... Hello all. I have a procedure that copies excel attachments from an Outloook folder and pastes them to a predefined drive and folder. Unfortunately, it only works if each file has a different name (I think if they are the same name, it overwrites the original). Of course, I have timesheets coming in via e-mail that all have the same file name. Is there a way to add a counter to the end of each name so all the files are copied in? Thanks! Here's the code I have: Sub SaveAtt() 'Saves attachments to a specified folder Dim ol As Outlook.Application Dim ns As NameSpace Dim Fldr As MAPIFolder Dim Mi As MailItem Dim Att As Attachment Set ol = New Outlook.Application Set ns = ol.GetNamespace("MAPI") Set Fldr = ns.Folders("Public Folders").Folders("All Public Folders").Folders("Timesheet") For Each Mi In Fldr.Items If Mi.Attachments.Count 0 Then For Each Att In Mi.Attachments Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename Next Att End If Next Mi Set Att = Nothing Set Mi = Nothing Set Fldr = Nothing Set ns = Nothing Set ol = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save files as different names
Change this
Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename to this iFile = iFile + 1 Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename & Cstr(iFile) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steph" wrote in message ... Hello all. I have a procedure that copies excel attachments from an Outloook folder and pastes them to a predefined drive and folder. Unfortunately, it only works if each file has a different name (I think if they are the same name, it overwrites the original). Of course, I have timesheets coming in via e-mail that all have the same file name. Is there a way to add a counter to the end of each name so all the files are copied in? Thanks! Here's the code I have: Sub SaveAtt() 'Saves attachments to a specified folder Dim ol As Outlook.Application Dim ns As NameSpace Dim Fldr As MAPIFolder Dim Mi As MailItem Dim Att As Attachment Set ol = New Outlook.Application Set ns = ol.GetNamespace("MAPI") Set Fldr = ns.Folders("Public Folders").Folders("All Public Folders").Folders("Timesheet") For Each Mi In Fldr.Items If Mi.Attachments.Count 0 Then For Each Att In Mi.Attachments Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename Next Att End If Next Mi Set Att = Nothing Set Mi = Nothing Set Fldr = Nothing Set ns = Nothing Set ol = Nothing End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save files as different names
Hi Bob,
Thanks for the response. I tried your code, and it added the counter at the end of the file extension. So the files now look like: timesheet.xls1 timesheet.xls2 timesheet.xls3 Can the counter be put before the file extension? "Bob Phillips" wrote in message ... Change this Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename to this iFile = iFile + 1 Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename & Cstr(iFile) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steph" wrote in message ... Hello all. I have a procedure that copies excel attachments from an Outloook folder and pastes them to a predefined drive and folder. Unfortunately, it only works if each file has a different name (I think if they are the same name, it overwrites the original). Of course, I have timesheets coming in via e-mail that all have the same file name. Is there a way to add a counter to the end of each name so all the files are copied in? Thanks! Here's the code I have: Sub SaveAtt() 'Saves attachments to a specified folder Dim ol As Outlook.Application Dim ns As NameSpace Dim Fldr As MAPIFolder Dim Mi As MailItem Dim Att As Attachment Set ol = New Outlook.Application Set ns = ol.GetNamespace("MAPI") Set Fldr = ns.Folders("Public Folders").Folders("All Public Folders").Folders("Timesheet") For Each Mi In Fldr.Items If Mi.Attachments.Count 0 Then For Each Att In Mi.Attachments Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename Next Att End If Next Mi Set Att = Nothing Set Mi = Nothing Set Fldr = Nothing Set ns = Nothing Set ol = Nothing End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save files as different names
my code showed you how to do that.
-- Regards, Tom Ogilvy "Steph" wrote in message ... Hi Bob, Thanks for the response. I tried your code, and it added the counter at the end of the file extension. So the files now look like: timesheet.xls1 timesheet.xls2 timesheet.xls3 Can the counter be put before the file extension? "Bob Phillips" wrote in message ... Change this Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename to this iFile = iFile + 1 Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename & Cstr(iFile) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steph" wrote in message ... Hello all. I have a procedure that copies excel attachments from an Outloook folder and pastes them to a predefined drive and folder. Unfortunately, it only works if each file has a different name (I think if they are the same name, it overwrites the original). Of course, I have timesheets coming in via e-mail that all have the same file name. Is there a way to add a counter to the end of each name so all the files are copied in? Thanks! Here's the code I have: Sub SaveAtt() 'Saves attachments to a specified folder Dim ol As Outlook.Application Dim ns As NameSpace Dim Fldr As MAPIFolder Dim Mi As MailItem Dim Att As Attachment Set ol = New Outlook.Application Set ns = ol.GetNamespace("MAPI") Set Fldr = ns.Folders("Public Folders").Folders("All Public Folders").Folders("Timesheet") For Each Mi In Fldr.Items If Mi.Attachments.Count 0 Then For Each Att In Mi.Attachments Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename Next Att End If Next Mi Set Att = Nothing Set Mi = Nothing Set Fldr = Nothing Set ns = Nothing Set ol = Nothing End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save files as different names
Sorry Tom.....didn't see your post. Thank you!!!
"Tom Ogilvy" wrote in message ... my code showed you how to do that. -- Regards, Tom Ogilvy "Steph" wrote in message ... Hi Bob, Thanks for the response. I tried your code, and it added the counter at the end of the file extension. So the files now look like: timesheet.xls1 timesheet.xls2 timesheet.xls3 Can the counter be put before the file extension? "Bob Phillips" wrote in message ... Change this Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename to this iFile = iFile + 1 Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename & Cstr(iFile) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steph" wrote in message ... Hello all. I have a procedure that copies excel attachments from an Outloook folder and pastes them to a predefined drive and folder. Unfortunately, it only works if each file has a different name (I think if they are the same name, it overwrites the original). Of course, I have timesheets coming in via e-mail that all have the same file name. Is there a way to add a counter to the end of each name so all the files are copied in? Thanks! Here's the code I have: Sub SaveAtt() 'Saves attachments to a specified folder Dim ol As Outlook.Application Dim ns As NameSpace Dim Fldr As MAPIFolder Dim Mi As MailItem Dim Att As Attachment Set ol = New Outlook.Application Set ns = ol.GetNamespace("MAPI") Set Fldr = ns.Folders("Public Folders").Folders("All Public Folders").Folders("Timesheet") For Each Mi In Fldr.Items If Mi.Attachments.Count 0 Then For Each Att In Mi.Attachments Att.SaveAsFile "H:\Timesheet Data\" & Att.Filename Next Att End If Next Mi Set Att = Nothing Set Mi = Nothing Set Fldr = Nothing Set ns = Nothing Set ol = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lost files, names of files still exist | Excel Discussion (Misc queries) | |||
Unable to save XLSX files without using 'Save As' and different na | Setting up and Configuration of Excel | |||
Where does Save As Automatic Backup save its files? | Excel Discussion (Misc queries) | |||
save multiple files with different names in other drive using macr | Excel Discussion (Misc queries) | |||
Change names of files in a folder to match names in Excel Column | Excel Programming |