Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input the filename into Excel Sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input the filename into Excel Sheet
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 "Li Jianyong" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input the filename into Excel Sheet
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 "Office_Novice" wrote in message ... 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 "Li Jianyong" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input the filename into Excel Sheet
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 "Office_Novice" wrote: 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 "Li Jianyong" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input the filename into Excel Sheet
Thanks both experts, the routine provided by you are helpful. Appreiciate your help,appreciate this nice discussion group. "Peter T" wrote: 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 "Office_Novice" wrote in message ... 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 "Li Jianyong" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add a input box to ask for a filename to macro | Excel Discussion (Misc queries) | |||
Protecting excel sheet for data input | Excel Discussion (Misc queries) | |||
Protecting excel sheet for data input | Excel Programming | |||
Input filename at start of macro | Excel Programming | |||
input mask in excel sheet | Excel Worksheet Functions |