Getting VBA to recognise a file using only part of the file name as the rest is variable?

[booms]

Hi,

I’m trying to set up a VBA macro to copy a list of files to another directory.

Up until now I’ve been using FileCopy and building up the directory path of the source file and telling it what output directory to use – nice and straightforward

There’s now been a change to the naming convention of the files however that has broken this.

Whereas the source files used to be called something like

Accounting_File_1_12032018.txt

(and it was easy to get the macro to recognise this with a bit of code to deal with the changing date)

However, now all the source files are preceded by a long string of alphanumeric random characters which may change – but the length of which is always the same e.g.

Asd8fa9f78sd7f9a87df9s7f_ Accounting_File_1_12032018.txt
8asdf84978sdf7a98sdf7s9e_Accounting_File_1_13032018.txt

Is there any way I can get my macro to identify the correct files only by using the right-hand side of the name of the file (which can be predicted)

Thanks

 

[Joe4]

Can you post your current VBA code, and we can see if we can help you amend it to do what you want?

 

[Legacy 421546]

If the prefix is of constant length (26 in this case):

Sub Test()
Dim fileName As String
Dim length As Integer
fileName = "Asd8fa9f78sd7f9a87df9s7f_ Accounting_File_1_12032018.txt"
length = Len(fileName)
Debug.Print Right(fileName, length - 26)
End Sub

Result:
Accounting_File_1_12032018.txt

 

[booms]

If the prefix is of constant length (26 in this case):

Sub Test()
Dim fileName As String
Dim length As Integer
fileName = "Asd8fa9f78sd7f9a87df9s7f_ Accounting_File_1_12032018.txt"
length = Len(fileName)
Debug.Print Right(fileName, length - 26)
End Sub

Result:
Accounting_File_1_12032018.txt

Hi,

Thanks for your reply - but that doesn't work unfortunately because the part on the left (the prefix)is truly variable – it’ll change every day and can’t be predicted. So we can’tinclude that in the VBA to identify the file. Also the length of the prefix betweendifferent files does not seem to be consistent.<o> </o>
What is consistent is the right hand side of the file e.g.the bit that says
<o> </o>
Accounting_File_1_12032018.txt<o></o>
<o> </o><o></o>
<o></o>
Is there any wayto identify the file from the VBA using just that?

 

[Legacy 421546]

Option Explicit
Sub Test()
Dim fileName As String
Dim length As Integer
Dim pos As Integer
fileName = "exta extra Asd8fa9f78sd7f9a87df9s7f_ Accounting_File_1_12032018.txt"
pos = InStr(fileName, "Accounting_File")
If pos > 0 Then
    length = Len(fileName)
    Debug.Print Right(fileName, length - (pos - 1))
End If
End Sub

 

[booms]

Option Explicit
Sub Test()
Dim fileName As String
Dim length As Integer
Dim pos As Integer
fileName = "exta extra Asd8fa9f78sd7f9a87df9s7f_ Accounting_File_1_12032018.txt"
pos = InStr(fileName, "Accounting_File")
If pos > 0 Then
    length = Len(fileName)
    Debug.Print Right(fileName, length - (pos - 1))
End If
End Sub

Thanks JohnnyJ - but the bit where you have:

fileName = "exta extra Asd8fa9f78sd7f9a87df9s7f_ Accounting_File_1_12032018.txt"

I can't have that in the VBA as the prefix will change unpredictably every day.

It may be 'exta extra Asd8fa9f78sd7f9a87df9s7f' on day 1, but it won't be on day 2.

 

[Legacy 421546]

Please experiment with the code. Change prefix

 

[Joe4]

I repeat what I said in my first reply:

Can you post your current VBA code, and we can see if we can help you amend it to do what you want?

 

[booms]

I repeat what I said in my first reply:

Thanks Joe,

Existing code below. To be clear, all it’s doing is working it’s way down a standard list of files, finding them, and copying them to another directory. The process used to be very straightforward.

Unfortunately this code is no longer fit for purpose as an alphanumeric prefix, different for each file, and changing everyday – has been added to the files in the source directory.


Sub SMARTcopy()

Dim SourcePath As String, basePath As String
Dim OutPath As String
Dim repName As String
Dim dateStr As String
Dim i As Integer

ActiveSheet.Calculate
i = 8

dateStr = Format(Cells(3, 3), "yyyymmdd")
basePath = slStr(Cells(5, 3))
OutPath = slStr(Cells(4, 3)) & slStr(dateStr) '& slStr(Cells(2, 3))
On Error Resume Next
MkDir (OutPath)
OutPath = OutPath & slStr(Cells(2, 3))
MkDir (OutPath)
On Error GoTo err1

Do While Cells(i, 4) <> ""
If Cells(i, 3) <> "" Then SourcePath = slStr(Cells(i, 3))
FileCopy basePath & SourcePath & Cells(i, 4), OutPath & Cells(i, 4)
i = i + 1
Loop


MsgBox (i - 8 & " files copied to " & vbNewLine & OutPath)


err1:
If Err.Number = 53 Then
erroBox = MsgBox(i - 8 & " files copied ok. " & vbNewLine & Cells(i, 4) & " did not copy!", vbExclamation, "Dodgy File")
ElseIf Err.Number <> 0 Then
MsgBox (Err.Description)
End If


End Sub


Private Function slStr(chkStr As String) As String

If Right(chkStr, 1) = "" Then
slStr = chkStr
Else
slStr = chkStr & ""
End If


End Function

 

[Joe4]

OK, I see that you are feeding in lots of inputs and the like. So I will give you a simplified example that I think will show you how to do what you want, so then you just need to incorporate the logic into your code.

The following code will take any CSV file that has the phrase "Accounting_File" anywhere in its name, and move it from a source folder to destination folder.

Sub MyFileCopy()

    Dim fName As String
    Dim srcFldr As String
    Dim dstFldr As String
    
    srcFldr = "C:\Temp\"
    dstFldr = "C:\Test\"
    
    fName = Dir(srcFldr & "*Accounting_File*.csv")
    Do While fName <> ""
        FileCopy srcFldr & fName, dstFldr & fName
        fName = Dir
    Loop
    
End Sub