Renameing Files

[JohnLee]

Good day folks,

I hoping someone can help me with the following:

I have a file structure that is created by a scanning system which I can not change, and what I want to do is to rename a tiff file that resides in the last folder in that structure with the name of that folder, there only ever be one tiff file in that folder.

Here is the file structure:

\\PRDFS01\DocumentArchiving\CustomerRelations\Complaints

Now under this last folder there is a further file structure whereby the folder is always given the current date in the following format yyyymmdd.

Then beneath this file structure is another folder which always has the following date format ddmmyy.

And then finally the last folder structure which is has a name convention as follows and will always have this format 000C or 000R followed by 4 numerical characters for example:

000C0001
000C0002
000C0003 etc

000R0001
000R0002
000R0003 etc

Underneath each of these folders will be a tiff image and what I would like to do is to rename that tif image to the name of the final folder, so for example

42B4C5D4-1553-4C23-9AD2-85BC074FE84A.tif would be come 000C0001.tif

Now to add to this complexity I would like to be able tell get the code to prompt for a folder date at the yyyymmdd folder structure level and then to target all the folders under the ddmmyy level and if a tiff file exists to change it’s name to that of the folder in which it resides under for example all folders as shown below.

\\PRDFS01\DocumentArchiving\CustomerRelations\Complaints\20131002\021013

I hope this is clear enough of what it is I’m trying to achieve any help would be most appreciated.

Below is some code that I have identified that might be what I need, but I’m really unclear on how to get it to do what I need.

[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] filenum [COLOR=blue]As Integer[/COLOR][/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] tmp [COLOR=blue]As String[/COLOR] [/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] FS [COLOR=blue]As[/COLOR] FileSystemObject                                                                                                                          [/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] Folder [COLOR=blue]As[/COLOR] Folder                                                                                                                                [/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] subFolder [COLOR=blue]As[/COLOR] Folder                                                                                                                             [/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] File [COLOR=blue]As[/COLOR] File                                                                                                                                    [/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] TifFilePath                                                                                                                                    [/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] NameOfFile                                                                                                                                      [/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] FileNameWithExt                                                                                                                                 [/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] strTemp [COLOR=blue]As String[/COLOR]   [/FONT]
[FONT=Times New Roman][COLOR=blue]Dim[/COLOR] FileLoc [COLOR=blue]As String[/COLOR] [/FONT]
 
[FONT=Times New Roman][COLOR=blue]Const[/COLOR] ForReading = 1                                                                                                                                [/FONT]
 
[B][COLOR=red][FONT=Times New Roman]Not sure of how to prompt for Date for folder search[/FONT][/COLOR][/B]
 
[FONT=Times New Roman][COLOR=blue]Set[/COLOR] FS = CreateObject("Scripting.FileSystemObject") [/FONT]
 
[FONT=Times New Roman]TifFilePath = "\\PRDFS01\Document Archiving\Customer Relations\Complaints”[/FONT]
 
[FONT=Times New Roman][COLOR=blue]For Each[/COLOR] File [COLOR=blue]In[/COLOR] Folder.Files                               [COLOR=green]'Loops through Folders looking for Files[/COLOR][/FONT]
[FONT=Times New Roman]     strTemp = Mid$(File.Name, InStrRev(File.Name, "\") + 1) [COLOR=green]'Assign the tif file name to the strTemp variable[/COLOR] [/FONT]
[FONT=Times New Roman]     FileLoc = Folder & "\" & File.Name                      [COLOR=green]'Assign the Folder and File.Name to the FileLoc variable[/COLOR][/FONT]
 
[FONT=Times New Roman] [COLOR=blue]Set[/COLOR] Folder = FS.GetFolder(TifFilePath)      [COLOR=green]'Open the tif file folders[/COLOR][/FONT]
 
[FONT=Times New Roman] [COLOR=blue]For Each[/COLOR] subFolder [COLOR=blue]In[/COLOR] Folder.SubFolders      [COLOR=green]'Loops through the Folders looking for SubFolders[/COLOR][/FONT]
[FONT=Times New Roman]     [COLOR=blue]For Each[/COLOR] File [COLOR=blue]In[/COLOR] subFolder.Files                 [COLOR=green]'Loops through Subfolders looking for Files[/COLOR][/FONT]
[FONT=Times New Roman]         NameOfFile = GetAttr("File.Name")      [COLOR=green]'Get the tif file name[/COLOR][/FONT]
[FONT=Times New Roman]         [COLOR=blue]If[/COLOR] Right(File.Name, 4) = ".tif" [COLOR=blue]Then[/COLOR]     [COLOR=green]'If the file found has a ".tif" extension then[/COLOR][/FONT]
[FONT=Times New Roman]             FileNameWithExt = Mid$(File.Name, InStrRev(File.Name, "\") + 1)      [COLOR=green]'Assign the File Name with its extension type to the FileNameWithExt variable[/COLOR][/FONT]
[FONT=Times New Roman]             strTemp = Mid$(File.Name, InStrRev(File.Name, "\") + 1)      [COLOR=green]'Assign the tif file name to the strTemp variable[/COLOR][/FONT]
[FONT=Times New Roman]             NameOfFile = Left$(strTemp, InStrRev(strTemp, ".") - 1)      [COLOR=green]'Assign the name of the tif file without the extension type to the NameOfFile variable[/COLOR][/FONT]
[FONT=Times New Roman]             subFilePath = Left$(subFolder, InStrRev(subFolder, "\"))      [COLOR=green]'Assign the Directory location of the sub file path to the SubFilePath variable[/COLOR][/FONT]
[FONT=Times New Roman]             SubFolderName = Mid$(subFolder, InStr(3, subFolder, "\"))     [COLOR=green]'Assign the name of the sub folder to the SubFolderName variable[/COLOR] [/FONT]
[FONT=Times New Roman]             FileLoc = subFolder & "\" & File.Name     [COLOR=green]'Assign the subFolder and File.Name to the FileLoc variable[/COLOR][/FONT]
[FONT=Times New Roman]                 [B][COLOR=red]Not sure of what to put here to rename the tif file[/COLOR][/B][/FONT]
[FONT=Times New Roman]         [COLOR=blue]End If[/COLOR]                                                                                                                         [/FONT]
[FONT=Times New Roman]     [COLOR=blue]Next[/COLOR][/FONT]
[COLOR=blue][FONT=Times New Roman]Next[/FONT][/COLOR]

Any assistance would be most appreciated.

John

 

[mrojas]

As for the renaming, why not use the aFSO.CopyFile Source, Destination then use the Kill command to delete the old file?

 

[JohnLee]

Thank you for your response, but I'm not looking to delete the original file, that would be retained in it's original location, I'm looking to copy the file and then rename it in the same location. The problem I'm having is working out how to give the file the name of the folder in which the original file exists and how to write code that searches for a folder according to a given variable via a prompt [i.e. input box]