Need help with a Find Macro

[copleyr]

Hello!

I am in desperate need of a macro.

1: On one spreadsheet, I need a macro that can look into a drive (i.e. C:\Program Files\excel files_folder).

2: The list of excel files that are in this folder each have a specific name (i.e. Math001, Math002, Math003)

Is there a way that I can get a macro that can look into this folder and list the first 6 characters of each file in the folder?

I have thousands of these constantly changing files at work and am in desperate need of a macro that can do this.


Thank you in advance!!!

 

[Blade Hunter]

Sub ListFiles()
Dim MyPathName As String
Dim MyFileName As String
Dim NumChars As Long
Dim X As Long
    NumChars = 6 'Change this to the number of characters you wish to return
    MyPathName = "C:\Program Files\excel files_folder\*.xl*" 'Change this to the folder and filetypes you want to return
    MyFileName = Dir(MyPathName)
    Do While MyFileName <> ""
        X = X + 1
        Sheet1.Cells(X, 1) = Left(MyFileName, NumChars)
        MyFileName = Dir
    Loop
End Sub

Cheers

Dan

 

[copleyr]

Thank you so much!

It worked perfectly when I accessed my hard drive.

However, when I tried to get it to access a drive on a network I am connected to, it wouldn't do anything.

Will I need to give it a new path? I used the same path as my C: drive and it worked well. When I tried to get it to access a K: drive on my network, nothing happened.

Thanks!

 

[Blade Hunter]

What are you putting in here:

MyPathName = "C:\Program Files\excel files_folder\*.xl*"

 

[copleyr]

Instead of a C:\Program Files\excel files_folder

I am inputing a K:\xxxxx\xxxx

 

[copleyr]

Nevermind I got it to work! I wasn't putting a "\" at the end of the path.

Thanks again so much@!