Identify and Rename as text files

[DanScott]

I receive 22 files everyweek. They are automatically generated by a mainframe and output in a text file format, the file names are something to the effect of: 0123shares_1_003.0903. All the numbers before the word "share" change weekly and all the numbers after the 1_ change weekly. I would like to automate the renameing of these files to Shares1.txt, but because the files change names weekly I cannot use the Name function. I have to do this to 22 files and would like very much to automate this ie.... Shares1, Shares2, Shares3.

 

[DaltonVB]

This is a lot of code and can be tuned quite a bit. Well, here is the quick and dirty. I haven't checked the code with your file names so... there may be some tweeking required.

Private Sub Command3_Click()
    ' Loop through the directory specified in strDirPath and save each
    ' file name in an array, then use that array to save a copy of each file the way you want
    
    Dim strTempName As String
    Dim strSharesName As String
    Dim varFile1() As Variant
    Dim varFile2() As Variant
    Dim lngUScore1 As Long
    Dim lngUScore2 As Long
    Dim lngFileCount As Long
    Dim lngIdx As Long
    Dim strdirpath As String
    Dim oFS As Object
    strdirpath = CurrentProject.Path
    
    Set oFS = CreateObject("Scripting.FileSystemObject")
    ' Make sure that strDirPath ends with a "\" character.
    If Right(strdirpath, 1) <> &quot;\&quot; Then
        strdirpath = strdirpath & &quot;\&quot;
    End If
    
    ' Make sure strDirPath is a directory.
    'MsgBox GetAttr(strDirPath)
    If GetAttr(strdirpath) = vbDirectory Or GetAttr(strdirpath) = 48 Or GetAttr(strdirpath) = 54 Or GetAttr(strdirpath) = 49 Or GetAttr(strdirpath) = 17 Then
    
        strTempName = Dir(strdirpath, vbDirectory)
        Do Until Len(strTempName) = 0
            
            ' Exclude &quot;.&quot;, &quot;..&quot;.
            If (strTempName <> &quot;.&quot;) And (strTempName <> &quot;..&quot;) Then
                ' Make sure we do not have a sub-directory name.
                If (GetAttr(strdirpath & strTempName) And vbDirectory) <> vbDirectory Then
                    ' Increase the size of the array
                    ' to accommodate the next file
                    ' and add the filename to the array.
                    ReDim Preserve varFile1(lngFileCount)
                    varFile1(lngFileCount) = strTempName
                    lngFileCount = lngFileCount + 1
                End If
            End If
            ' Use the Dir function to find the next filename.
            strTempName = Dir()
        Loop
        ReDim varFile2(lngFileCount, 1) As Variant
        For lngIdx = 0 To lngFileCount - 1
            varFile2(lngIdx, 0) = varFile1(lngIdx)
            lngUScore1 = InStr(1, varFile1(lngIdx), &quot;_&quot;)
            lngUScore2 = InStr(lngUScore1, varFile1(lngIdx), &quot;_&quot;)
            varFile2(lngIdx, 0) = strdirpath & varFile1(lngIdx)
            varFile2(lngIdx, 1) = strdirpath & &quot;shares&quot; & Mid(varFile1(lngIdx), lngUScore1 + 1, lngUScore2 - lngUScore1) & &quot;.txt&quot;
                
            oFS.CopyFile varFile2(lngIdx, 0), varFile2(lngIdx, 1)
            oFS.DeleteFile varFile2(lngIdx, 0)
        Next
        
    End If
End Sub

 

[DanScott]

Thanks for the code, I input it and began to run it when I got my first error at:
lngUScore2 = InStr(lngUScore1, varFile1(lngIdx), "_&quot
the error was invalid procedure call or argument.
I was understanding the code up to the loop portion. Do you think you could give me some idea of what it is doing past that point.(I really enjoy when I can go aaaaahhh.)
thanks

 

[DaltonVB]

It's a lot to digest and it was written for a different purpose than you are using it. I made a file/folder dialog popup for users with Access2000. Now I just use a cDialog class.

Here's what's going on with the function. The first loop is
getting all of the files from the directory you specified and assigning them to an array.

The second loop is assinging the value from array1 into array2 and creating a new name for the file and storing it in the second index of the array. Then, within this loop, you use array2 to pass file specs to the fileSystemObject
to copy and delete each file in the directory.

Here is a step by step.
This is where you prime you file or folder string.
strTempName is being passed the first file or folder in the
path that you set (strdirpath). This also sets the path
for the rest of the loop.

strTempName = Dir(strdirpath, vbDirectory)

Here, you are saying, if strTempName isn't an empty string, loop again.

Do Until Len(strTempName) = 0

Now, you want to exclude the . and .. directories

' Exclude ".", "..".
            If (strTempName <> &quot;.&quot;) And (strTempName <> &quot;..&quot;) Then

Now, we want to make sure we are looking at files and not folders.

If (GetAttr(strdirpath & strTempName) And _
         vbDirectory) <> vbDirectory Then

Here, you are increasing the array size
assigning the current file to the array
at your current file count
and then, incrementing your file counter.

' Increase the size of the array
' to accommodate the next file
' and add the filename to the array.
ReDim Preserve varFile1(lngFileCount)
'Populate the array with the current file
varFile1(lngFileCount) = strTempName
lngFileCount = lngFileCount + 1

Get your next file or folder name. Every time you call
this function, it will get the next file or folder
available in the directory you set earlier. If you call the function and it returns an empty string, there are no more files or folders in the directory.

' Use the Dir function to find the next filename.
      strTempName = Dir()

Now, you are going to set the second arrays size based on
the number of files found in the directory you specified.

ReDim varFile2(lngFileCount, 1) As Variant

Here, you are setting up a forNext loop.
lngIdx will be used as the index in your arrays.
lngFildCount is the number of items in your first array
that was populated during the first step.

For lngIdx = 0 To lngFileCount - 1

The next part is confusing. I see also that I made a mistake in my code.
This line was not necessary so remove it.

varFile2(lngIdx, 0) = varFile1(lngIdx)

Here, you are disecting the file name to pull out
"Shares_1". After re-reading your post, I find that this is
not necessary so... take it out.

lngUScore1 = InStr(1, varFile1(lngIdx), "_")
lngUScore2 = InStr(lngUScore1, varFile1(lngIdx), "_")

You have a two dimensional array. Index 1 will relate to the index in your first array (the count of all files found)
The second index will be for the old name of the file and new name for the file. Index 0 will be the old name and
index 1 will be the new name.

'bring in old name of file
varFile2(lngIdx, 0) = strdirpath & varFile1(lngIdx)

I have changed the way a name is assigned to the file.
Now it is just "Shares" & current file index & ".txt"

'create new name of file
varFile2(lngIdx, 1) = strdirpath & "shares" & lngIdx & ".txt"

Now we are going to use that information
you are going to call the fileSystemObject and use the CopyFile function to rename the file to the new name.

oFS.CopyFile varFile2(lngIdx, 0), varFile2(lngIdx, 1)

Now we are deleting the old file that was just copied and renamed'

oFS.DeleteFile varFile2(lngIdx, 0)

 

[DanScott]

The code works, only it renames all the files in the directory and I cannot determine if it is in any certain order. My thought was I would get the basic code and then alter it to be exact for my application, but this seems to be more than I can swallow. Here it is: 22 files generated by mainframe, 7 are consistantly named something like 99_HHC.Shares.1_##### (the # are the only numbers that very each week) 99_HHC.Shares.2_#####. These files need to be renamed to SHARES1.txt, SHARES2.txt, but the number needs to be the first number in the filename right after the shares., I then have 8 files with the same format but instead of SHARES it has LOANS, and then 7 with CERTs. So, it seems if I could extract the Names and then the number, this would be work, could you please assist me.

 

[DaltonVB]

Try this:
I have added two functions.

f_buildNewName 'This one creates the new name based off of the old name
f_fileBase 'This one creates the base name i.e. 'Shares','Loans','Certs'

Private Sub Command3_Click()
    ' Loop through the directory specified in strDirPath and save each
    ' file name in an array, then use that array to save a copy of each file the way you want
    Dim strTempName As String
    Dim strSharesName As String
    Dim varFile1() As Variant
    Dim varFile2() As Variant
    Dim lngFileCount As Long
    Dim lngIdx As Long
    Dim strdirpath As String
    Dim oFS As Object
    strdirpath = CurrentProject.Path
    
    Set oFS = CreateObject("Scripting.FileSystemObject")
    ' Make sure that strDirPath ends with a "\" character.
    If Right(strdirpath, 1) <> &quot;\&quot; Then
        strdirpath = strdirpath & &quot;\&quot;
    End If
    
    ' Make sure strDirPath is a directory.
    'MsgBox GetAttr(strDirPath)
    If GetAttr(strdirpath) = vbDirectory Or GetAttr(strdirpath) = 48 Or GetAttr(strdirpath) = 54 Or GetAttr(strdirpath) = 49 Or GetAttr(strdirpath) = 17 Then
    
        strTempName = Dir(strdirpath, vbDirectory)
        Do Until Len(strTempName) = 0
            
            ' Exclude &quot;.&quot;, &quot;..&quot;.
            If (strTempName <> &quot;.&quot;) And (strTempName <> &quot;..&quot;) Then
                ' Make sure we do not have a sub-directory name.
                If (GetAttr(strdirpath & strTempName) And vbDirectory) <> vbDirectory Then
                    ' Increase the size of the array
                    ' to accommodate the next file
                    ' and add the filename to the array.
                    ReDim Preserve varFile1(lngFileCount)
                    varFile1(lngFileCount) = strTempName
                    lngFileCount = lngFileCount + 1
                End If
            End If
            ' Use the Dir function to find the next filename.
            strTempName = Dir()
        Loop
        ReDim varFile2(lngFileCount, 1) As Variant
        For lngIdx = 0 To lngFileCount - 1
            varFile2(lngIdx, 0) = strdirpath & varFile1(lngIdx)
            varFile2(lngIdx, 1) = f_buildNewName(strdirpath, varFile1(lngIdx))
                
            oFS.CopyFile varFile2(lngIdx, 0), varFile2(lngIdx, 1)
            oFS.DeleteFile varFile2(lngIdx, 0)
        Next
        
    End If
End Sub


Private Function f_buildNewName(szPath As String, ByVal szFileName As String)
    Dim szNewFile As String
    Dim i As Integer
    Dim j As Integer
    Dim szFileBase As String

    szFileBase = f_fileBase(szFileName)
    
    If InStr(1, szFileName, szFileBase) Then
        i = InStr(1, szFileName, szFileBase)
        j = InStr(i, szFileName, &quot;.&quot;)
        
        'This adds the number after 'Shares.' to the end of the new name
        'i.e.  99_HHC.Shares.1_##### will become 'Shares1_'
        '      99_HHC.Shares.2_##### will become 'Shares2_'
        '      99_HHC.Shares.10_##### will become 'Shares10'
        szNewFile = szFileBase & Mid(szFileName, j + 1, 2)
        'if the number is less than 10, then trim the '_' off of the end
        If InStr(1, szNewFile, &quot;_&quot;) Then
            szNewFile = Left(szNewFile, Len(szNewFile) - 1)
        End If
    End If
    'return szNewFile
    f_buildNewName = szNewFile
        
End Function

Private Function f_fileBase(szFileName) As String
    If InStr(1, szFileName, &quot;Shares&quot;) Then
        f_fileBase = &quot;Shares&quot;
    ElseIf InStr(1, szFileName, &quot;Loans&quot;) Then
        f_fileBase = &quot;Loans&quot;
    ElseIf InStr(1, szFileName, &quot;Certs&quot;) Then
        f_fileBase = &quot;Certs&quot;
    Else
        f_fileBase = &quot;&quot;
    End If
End Function

 

[DanScott]

almost there, I watch the files be deleted, but no new files in their place? I get no errors, it is just not writing the new files to the directory.

 

[DaltonVB]

Here is the reason:

varFile2(lngIdx, 1) = f_buildNewName(strdirpath, varFile1(lngIdx))

change this line to read:

varFile2(lngIdx, 1) = strdirpath & f_buildNewName(strdirpath, varFile1(lngIdx))

I forgot to add the file spec to the begining of the name.
CopyFile didn't know what to do with that so it did nothing.

 

[DanScott]

Works Great, couldn't be happier, you sir, are the Guru. Thank you very much for your assistance.