do*****@gov.nl.ca wrote:
Hi Salad,
If I have a folder with the following files:
00012.jpg
00012a.jpg
00012b.jpg
00012c.jpg
I would like to know how to return 00012c.jpg
I think I need to use the Chr() function to return the ANSI value to
do it but I don't know how
Maybe first Parse out the letter by striping off '00012' and '.jpg'
parts.
Here is more of my code
Sub AutoUpdate(strSourceFile)
Dim file, v
Dim fs, strDestFolder, strDestFile, strAlphaPart, strNumPart,
strDestFileNew, strFileName
Dim strAlphaInput As String
Dim dtmSourceFileDate As Date
Dim myForm As Form
Dim lngI As Long, lngFileName As Long
Dim varFile As Variant
Dim intFileName As Integer
Dim lngAlpha As Long
Set myForm = Screen.ActiveForm
'If the user has updated or added a photo in N:\wwwroot\WORKS
\BLDGPIC1 directory, Then...
'First check to see if the field PhotoDateModified in BLDGMSTR has
the same photo date if
'not then synchronize. Use the DateLastModified property of the
FileSystemObject as this
'property does not change when a file is copied from one directory
to another.
Set fs = CreateObject("Scripting.FileSystemObject")
Set strSourceFile = fs.getfile(strSourceFile) '=N:\wwwroot\WORKS
\BLDGPIC1\00012.jpg
dtmSourceFileDate = strSourceFile.DateLastModified
If dtmSourceFileDate <myForm!txtPhotoDateLastModified Then 'A
new picture has been added to N drive
myForm!txtPhotoDateLastModified = dtmSourceFileDate
'Synchronize the field PhotoDateModified in table BLDGMSTR
v = myForm!cboVOLUME
If v = "9" Then
v = "0"
End If
Set strDestFolder = fs.GetFolder("F:\User\BLDG
\FacilitiesFilingCabinet\Volume" & Trim(v) & "\" & Trim(myForm!
txtBLDGNUM) & "\" & Trim(myForm!txtSUBNAME))
strDestFile = Dir(strDestFolder & "\" & Trim(myForm!
txtPHOTO)) '00012.jpg
'if the destination directory is empty then simply copy the
file
If strDestFile = "" Then
FileCopy strSourceFile, strDestFolder & "\" & Trim(myForm!
txtPHOTO)
Else 'otherwise begin AutoUpdate
'Begin filtering files in the directory for FILENUM pattern and
parse out the file name.
For Each file In strDestFolder.files
If Left(file.Name, 5) = Trim(myForm!txtFILENUM) Then
strFileName = Left(file.Name, Len(file.Name) - 4)
lngAlpha = Len(strFileName)
For lngI = lngAlpha To 1 Step -1
'(strFileName) 'This is where i'm stuck
Debug.Print lngAlpha
Debug.Print strFileName
Next lngI
End If
Next file
End If
End If
'Name strDestFile As strDestFileNew 'Please Ignore
'FileCopy strSourceFile, strDestFile 'Please Ignore
Set myForm = Nothing
Set fs = Nothing
Set strSourceFile = Nothing
Set strDestFolder = Nothing
End Sub
Dim lngMax As String
DIm strAlpha As String 'extension
Dim strMax As String
Dim strF As String
Dim strMyForm As String
Dim strChar As String
Dim strNewFile As String
Dim intFor As Integer
Dim strHold As String
strF = file.Name
strMyForm = myForm!txtFILENUM
For Each file In strDestFolder.files
If Left(strF, 5) = Trim(strMyForm) Then
'remove prefix and ".jpg"
strAlpha = mid(strF,6,len(strF)-9)
If strF <"" Then
If Len(strAlpha) Len(strMax) then
'"aa" < "z" so check for len
strMax = strAlpha
Elseif strAlpha strMax then
strMax = strAlpha
Endif
Else
strMax = chr(64) 'first char before "a"
Endif
Endif
'you now have the highest extension. now increment
If LCase(Right(strMax,1)) = <"z" then
'if a, make b
strChar = Chr(asc(Right(strMax,1)) + 1)
strExt = left(strExt,len(strExt)-1) & strChar
Else
strChar = ""
For intFor = len(strMax) to 1 step -1
strChar = right(strMax,intfor,1)
If strChar <"z" then
strHold = strHold & Chr(asc(Right(strMax,1)) + 1)
If right(strMax,intFor,1)
If right(strMax,intFor,1) <"z" then
Next
Endif
endif
strNewFile = Left(strF, 5) & strExt & ".jpg"
msgbox "The old file was " & strF & " and the new is " & strNewFile