Question Help. I want to get my macro to check if a filename already exists, and if it does create next filename in a series.
E.g. if filename AKL200807-001.xls exists, then it would create AKL200807-002.xls
The first part of the filename "AKL200807-" is from a value in a cell, the 001 is the bit i'm having issues with...
I know probably I need to use and if/then, but not sure how to go about it... Any Suggestions
Answer The code below demonstrates how you can achieve this. Amend the commented lines as appropriate.
dim objFSO as object
dim i as integer
dim strFileNamePrefix as string
dim strPath as string
dim strUniqueFilename as string
strPath="d:\test\" ' This is the path to save your file - needs trailing /
strFileNamePrefix=Range("A1") ' This is your filename, e.g. "AKL200807-"
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) ' This is your unique filename
function Pad(intNum as integer, intLen as integer)
dim strX as string
strX=CStr(intNum)
Pad=Space(intLen-Len(strX)) & strX
end function
