Input the filename into Excel Sheet

[Li Jianyong]

Dear Experts,

I am asked to input the fillename as text into the Excel sheet. These files
are PDF formated. Like 19347-Autoline-6001004.pdf,it is thousands files. Is
anybody help me to provide me a solution to "read" these file names
automatically into my worksheets like:
A B C
1 19347 Autoline 6001004
2. 19348 autoline 5001045
.....

Eager to know the solution!

Li Jianyong

 

[Office_Novice]

Thats a good one try this.

Sub GetEm()
On Error Resume Next
Dim FolderPath 'Path to the folder to be searched for files
Dim objFSO 'The fileSystemObject
Dim objFolder 'The folder object
Dim colFiles 'Collection of files from files method
Dim objFile 'individual file object
Dim ws
Dim x
FolderPath = "E:\" ' Your File Path

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FolderPath)
Set colFiles = objFolder.Files
Set ws = ActiveWorkbook.Worksheets(1)
x = 1
For Each objFile In colFiles
ws.Cells(x, 1).Value = objFile.Name
ws.Cells(x, 2).Value = objFile.Size & " bytes"
x = x + 1
Next
End Sub

 

[Peter T]

Here's the same routine extended to "split" those dashes out of the pdf
filenames, and dump into three cells (some other minor changes too) -

Sub GetEm()
Dim FolderPath As String 'Path to the folder to be searched for files
Dim objFSO As Object 'The fileSystemObject
Dim objFolder As Object 'The folder object
Dim colFiles As Object 'Collection of files from files method
Dim objFile As Object 'individual file object
Dim ws As Worksheet
Dim x As Long
Dim pos As Long
Dim sFile As String
Dim va

FolderPath = "c:\my documents\" '<<<< Your File Path

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(FolderPath)
Set colFiles = objFolder.Files
Set ws = ActiveWorkbook.Worksheets(1)

For Each objFile In colFiles
' ws.Cells(x, 1).Value = objFile.Name
' ws.Cells(x, 2).Value = objFile.Size & " bytes"
sFile = objFile.Name
pos = 0
pos = InStr(5, sFile, ".pdf", vbTextCompare)
If pos Then
x = x + 1
va = Split(Left$(sFile, pos - 1), " ")
Cells(x, 1).Resize(, UBound(va) + 1).Value = va
End If
Next

End Sub

** don't forget to change "FolderPath" to your needs

Regards,
Peter T

 

[Li Jianyong]

Thanks very much for your help. It makes me very exciting. The result is big
step to my expected result. Now I can use built-in function to finish my
'split' job.

Best regards
Li Jianyong

 

[Li Jianyong]

Thanks both experts, the routine provided by you are helpful. Appreiciate
your help,appreciate this nice discussion group.