-
[vba]
Option Explicit
Sub Increment()
Dim pth As String
Dim fName1 As String, fName2 As String, fName As String
pth = BrowseForFolder
fName1 = Range("Cust").Value
fName2 = Range("ServCntA").Value
fName = fName1 & "_" & fName2 & "Posts" & "_" & Format(Date, "mmddyyyy") & "-v"
ActiveWorkbook.SaveAs pth & FCount(pth, fName)
End Sub
Function FCount(pth As String, fName As String)
fName = Split(fName, "-v")(0)
With Application.FileSearch
.NewSearch
.LookIn = pth
.Filename = fName
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
FCount = fName & "-v" & .FoundFiles.Count + 1
End If
End With
End Function
Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'''Code from kpuls, www.VBAExpress.com..portion of Knowledge base submission
Dim ShellApp As Object
Dim Drv As String
Dim fs As Object, d As Object
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, Range("A1").Value)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0
Set ShellApp = Nothing
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select
Exit Function
Invalid:
MsgBox "No folder selected", vbInformation
BrowseForFolder = False
End Function
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules