Find name of file in dir

[sal21]

Base myvar="102016", how to check if file in dir exists, and retrieve the complete name of file?

Possible without loop file in dir?

all file have a numeric name, but with variable length

[HansV]

Code: Select all

    Dim myFolder As String
    Dim myFile As String
    myFolder = "C:\Lavori_VB6\...\...\"
    myFile = Dir(myFolder & myVar & ".*")
    If myFile = "" Then
        MsgBox "File does not exist!"
    Else
        MsgBox "The complete filename is " & myFile
    End If

[sal21]

Code: Select all

    Dim myFolder As String
    Dim myFile As String
    myFolder = "C:\Lavori_VB6\...\...\"
    myFile = Dir(myFolder & myVar & ".*")
    If myFile = "" Then
        MsgBox "File does not exist!"
    Else
        MsgBox "The complete filename is " & myFile
    End If

[sal21]

Code: Select all

    Dim myFolder As String
    Dim myFile As String
    myFolder = "C:\Lavori_VB6\...\...\"
    myFile = Dir(myFolder & myVar & ".*")
    If myFile = "" Then
        MsgBox "File does not exist!"
    Else
        MsgBox "The complete filename is " & myFile
    End If

sorry bro...
based this code how to rename only all files with namefile.jpeg in namefile.jpg, delete old file , *.jpeg

[HansV]

Code: Select all

    Dim myFolder As String
    Dim myFile As String
    myFolder = "C:\Lavori_VB6\...\...\"
    myFile = Dir(myFolder & "*.jpeg")
    Do While myFile <> ""
        Name myFolder & myFile As myFolder & Replace(myFile, ".jpeg", ".jpg")
        myFile = Dir
    Loop

[sal21]

Code: Select all

    Dim myFolder As String
    Dim myFile As String
    myFolder = "C:\Lavori_VB6\...\...\"
    myFile = Dir(myFolder & myVar & ".*")
    If myFile = "" Then
        MsgBox "File does not exist!"
    Else
        MsgBox "The complete filename is " & myFile
    End If

bro...
is this correct to use a multi pattern for files in dir:

...
sPATTERN = "*.gif;*.bmp;*.jpg"

sPath = STRPATHIMG

sfile = Dir(sPath & sPATTERN & ".*")

...

[HansV]

No, you can process only one attachment at a time. So you'll have to use one loop for *.gif, one for *.bmp and one for *.gif.

[sal21]

No, you can process only one attachment at a time. So you'll have to use one loop for *.gif, one for *.bmp and one for *.gif.

OK.
Peraps, split into array and loop it, for example:

arrpattern="*.gif;*.jpg;*.bmp"

[HansV]

Yes:

Code: Select all

    Dim myFolder As String
    Dim myFile As String
    dim myExtension As Variant
    myFolder = "C:\Lavori_VB6\...\...\"
    For Each myExtension in Array("*.gif", "*.jpg", "*.bmp")
        myFile = Dir(myFolder & myExtention)
        Do While myFile <> ""
            ' Your code here
            ...
            myFile = Dir
        Loop
    Next myExtension

[Priyantha]

Dear All,

I get file names to an excel sheet from a folder, using the below macro. I tried to sort them in ascending order. but not sorted them in the correct order. please can anyone solve this issue?

Correct oder : 1-1.pdf, 1-2.pdf, 1-3.pdf ............... 1-10.pdf, 1-11.pdf......1-20.pdf..

After getting to the excel sheet: 1-1.pdf, 1-10.pdf, 1-11.pdf, 1-20.pdf........

My VBA Code :

Sub Get_Old_File_Name()
Dim myPath As String
Dim myFile As String
Dim r As Long

myPath = "C:\Users\User\Desktop\Maling Payadvice\NVQ\PaySlips"
myFile = Dir(myPath & "*.*")

If myFile = "" Then
MsgBox " No Files in the Folder"
Else

r = 4

Do While myFile <> ""
Cells(r, 5).Value = myFile
r = r + 1
myFile = Dir

Loop
MsgBox "Completed"
End If
End Sub

[HansV]

What is the largest number before the "-", and what is the largest number after the "-"?

[Priyantha]

Dear Hans,

As my Example, my original file's name is "1.pdf" (I can use any name I like as the file name, - number or letters). After splitting to many files they were displayed as 1.1.pdf, 1.2.pdf, 1.3.pdf......... 1.25.pdf, etc. the largest number before the "-" is "1" & largest number after the "-" is 54 (First file name: 1.1.pdf & Last file name 1.54.pdf)


Thanks,

Regarding

Priyantha

[HansV]

Try this:

Code: Select all

Sub Get_Old_File_Name()
    Dim myPath As String
    Dim myFile As String
    Dim r As Long

    myPath = "C:\Users\User\Desktop\Maling Payadvice\NVQ\PaySlips"
    myFile = Dir(myPath & "*.*")

    If myFile = "" Then
        MsgBox " No Files in the Folder"
    Else
        Application.ScreenUpdating = False
        r = 3
        Do While myFile <> ""
            r = r + 1
            Cells(r, 5).Value = myFile
            myFile = Dir
        Loop
        Call Sort_Names(Range("E4:E" & r))
        Application.ScreenUpdating = True
        MsgBox "Completed"
    End If
End Sub

Sub Sort_Names(rng As Range)
    rng.Offset(ColumnOffset:=1).EntireColumn.Insert
    rng.Offset(ColumnOffset:=1).FormulaR1C1 = "=-MID(RC[-1],FIND(""-"",RC[-1]),FIND(""."",RC[-1])-FIND(""-"",RC[-1]))"
    rng.Resize(ColumnSize:=2).Sort Key1:=rng(1, 2), Header:=False
    rng.Offset(ColumnOffset:=1).EntireColumn.Delete
End Sub