jaclynresendez
New Member
- Joined
- Jan 20, 2009
- Messages
- 19
Hello All,
I'm trying to get my Command Button to save the workbook as a unique name each time. Here I have it saving with a specific filename and, if it already exists it's SUPPOSED to add a number to the name. Like if WorkbookA1 already exists then it's to save as WorkbookA2, THEN sends it in an email. The problem is it'll save the first book as filename1 but will not go on to save the next as filename2 if filename1 already exists. This is the code I already have:
Dim objFSO As Object
Dim i As Integer
Dim strFileNamePrefix As String
Dim strPath As String
Dim strUniqueFilename As String
strPath = "\\Snnp-oa-001\snnopertns\AutoRun\NGE_"
strFileNamePrefix = Range("B25")
Set objFSO = CreateObject("Scripting.FileSystemObject")
i = 1
While objFSO.FileExists(strPath & strFileNamePrefix & Pad(i, 3))
i = i + 1
Wend
Set objFSO = Nothing
strUniqueFilename = strPath & strFileNamePrefix & Pad(i, 3) & ".xls"
ActiveWorkbook.SaveAs strUniqueFilename
ActiveWorkbook.SendMail Recipients:=myemail@company.com
Any help or a better way to do this would be greatly appreciated. Was trying to get this done today, but may not. Hopefully it'll be ready to go tomorrow???
I'm trying to get my Command Button to save the workbook as a unique name each time. Here I have it saving with a specific filename and, if it already exists it's SUPPOSED to add a number to the name. Like if WorkbookA1 already exists then it's to save as WorkbookA2, THEN sends it in an email. The problem is it'll save the first book as filename1 but will not go on to save the next as filename2 if filename1 already exists. This is the code I already have:
Dim objFSO As Object
Dim i As Integer
Dim strFileNamePrefix As String
Dim strPath As String
Dim strUniqueFilename As String
strPath = "\\Snnp-oa-001\snnopertns\AutoRun\NGE_"
strFileNamePrefix = Range("B25")
Set objFSO = CreateObject("Scripting.FileSystemObject")
i = 1
While objFSO.FileExists(strPath & strFileNamePrefix & Pad(i, 3))
i = i + 1
Wend
Set objFSO = Nothing
strUniqueFilename = strPath & strFileNamePrefix & Pad(i, 3) & ".xls"
ActiveWorkbook.SaveAs strUniqueFilename
ActiveWorkbook.SendMail Recipients:=myemail@company.com
Any help or a better way to do this would be greatly appreciated. Was trying to get this done today, but may not. Hopefully it'll be ready to go tomorrow???