Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder and filenames?
I want to list (from a known directory eg: E:\Customers)
the folder names (that branch off the Customers folder) into sheet1 and filenames in each folder into sheet2. Can this be done with VBA or do I need to resort to an API call? If anyone knows or has an idea, let me know here and at my email: Alternatively, In vb6 there is a control that lists all the files in a given directory, in a listbox. Is there a similar control in excel? Could I use this control in excel? I've got it but haven't tried it yet. regards Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder and filenames?
Mark,
A bit of code goes through all folders ands files from a given folder and lists them on a worksheet as hyperlinks, you should be able to adapt to what you want, both for the former and the latter requirement. There is no control that I know of that does it. Dim FSO As Object Dim cnt As Long Dim arfiles Dim level As Long Sub Folders() Dim i As Long Dim sFolder As String Set FSO = CreateObject("Scripting.FileSystemObject") arfiles = Array() cnt = -1 level = 1 sFolder = "C:\myTest" ReDim arfiles(1, 0) If sFolder < "" Then SelectFiles sFolder Worksheets.Add.Name = "Files" With ActiveSheet For i = LBound(arfiles, 2) To UBound(arfiles, 2) .Hyperlinks.Add Anchor:=.Cells(i + 1, arfiles(1, i)), _ Address:=arfiles(0, i), _ TextToDisplay:=arfiles(0, i) Next .Columns("A:Z").EntireColumn.AutoFit End With End If End Sub '----------------------------------------------------------------------- Sub SelectFiles(Optional sPath As String) '----------------------------------------------------------------------- Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object If sPath = "" Then Set FSO = CreateObject("SCripting.FileSystemObject") sPath = "c:\myTest" End If Set Folder = FSO.GetFolder(sPath) Set Files = Folder.Files For Each file In Files cnt = cnt + 1 ReDim Preserve arfiles(1, cnt) arfiles(0, cnt) = Folder.Path & "\" & file.Name arfiles(1, cnt) = level Next file level = level + 1 For Each fldr In Folder.Subfolders SelectFiles fldr.Path Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder and filenames?
Hi Mark
Application.FileSearch will also do the job but partially: With Application.FileSearch ..NewSearch ..LookIn = "E:\Customers" ..SearchSubFolders = True ..FileType = msoFileTypeAllFiles 'see choice in list If .Execute() 0 Then Worksheets("Sheet1").Cells(1, 1).Value = "File Name" For i = 2 To .FoundFiles.Count Worksheets("Sheet1").Cells(i, 1).Value = .FoundFiles(i) Next i Else MsgBox "No files where found in the specified folder", vbInformation, "No Match" End If End With HTH Cordially Pascal "Mark" a écrit dans le message de ... I want to list (from a known directory eg: E:\Customers) the folder names (that branch off the Customers folder) into sheet1 and filenames in each folder into sheet2. Can this be done with VBA or do I need to resort to an API call? If anyone knows or has an idea, let me know here and at my email: Alternatively, In vb6 there is a control that lists all the files in a given directory, in a listbox. Is there a similar control in excel? Could I use this control in excel? I've got it but haven't tried it yet. regards Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder and filenames?
in addition to Bob's code..
These will bring up standard dialogs for opening and browsing files. see VBA help for details office.FileDialog excel.GetOpenfileName Private Sub Test() Dim xlapp As Object Set xlapp = GetObject(, "excel.application") xlapp.dialogs(475).Show '475 = xlDialogFileFind End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Mark" wrote: I want to list (from a known directory eg: E:\Customers) the folder names (that branch off the Customers folder) into sheet1 and filenames in each folder into sheet2. Can this be done with VBA or do I need to resort to an API call? If anyone knows or has an idea, let me know here and at my email: Alternatively, In vb6 there is a control that lists all the files in a given directory, in a listbox. Is there a similar control in excel? Could I use this control in excel? I've got it but haven't tried it yet. regards Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re: Folder and filenames?
Hi Bob,
Thanks for the response. I will try your code. I have to write a macro that picks up the full path and address and xls file from the sheet, open it, copy a range of data, then paste that data in my main book, close the source book, goto next xls. and loop. What I am doing at the moment, but I think your code will work better, is using a vb6 userform with a directory list box control, a file list control, and a drive list control. THe main form has the drive list control. The user selects a drive because i am using drive E he may well have to access drive A to Z. A button exceutes to go. changes dir and drive. Another form loads but does not open. It holds the dir list, which loads info about current directory and drive the dir list. a macro writes to a text file all names of folders. another form loads, not show, with file listbox contents of sub folder of folder in previous list, writes to a text file all "*.xls" files. When all this is complete, about 500 folders/sub folders and 5,000 xls files, excel starts and automatically imports the list, then copies an unknown range, probably cell to last cell, to the main book. .... I am hoping that your macro can do what the vb6 utility (not quiter finished) is designed for. Sorry for all the banter, I just wanted you to know what it is all about and ps, I have not asked for a fee on this ptroject(yet) from the "client". Let me see if I can do it first! -----Original Message----- Mark, A bit of code goes through all folders ands files from a given folder and lists them on a worksheet as hyperlinks, you should be able to adapt to what you want, both for the former and the latter requirement. There is no control that I know of that does it. Dim FSO As Object Dim cnt As Long Dim arfiles Dim level As Long Sub Folders() Dim i As Long Dim sFolder As String Set FSO = CreateObject("Scripting.FileSystemObject") arfiles = Array() cnt = -1 level = 1 sFolder = "C:\myTest" ReDim arfiles(1, 0) If sFolder < "" Then SelectFiles sFolder Worksheets.Add.Name = "Files" With ActiveSheet For i = LBound(arfiles, 2) To UBound(arfiles, 2) .Hyperlinks.Add Anchor:=.Cells(i + 1, arfiles(1, i)), _ Address:=arfiles(0, i), _ TextToDisplay:=arfiles (0, i) Next .Columns("A:Z").EntireColumn.AutoFit End With End If End Sub '--------------------------------------------------------- -------------- Sub SelectFiles(Optional sPath As String) '--------------------------------------------------------- -------------- Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object If sPath = "" Then Set FSO = CreateObject ("SCripting.FileSystemObject") sPath = "c:\myTest" End If Set Folder = FSO.GetFolder(sPath) Set Files = Folder.Files For Each file In Files cnt = cnt + 1 ReDim Preserve arfiles(1, cnt) arfiles(0, cnt) = Folder.Path & "\" & file.Name arfiles(1, cnt) = level Next file level = level + 1 For Each fldr In Folder.Subfolders SelectFiles fldr.Path Next End Sub . -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Mark" wrote in message ... I want to list (from a known directory eg: E:\Customers) the folder names (that branch off the Customers folder) into sheet1 and filenames in each folder into sheet2. Can this be done with VBA or do I need to resort to an API call? If anyone knows or has an idea, let me know here and at my email: Alternatively, In vb6 there is a control that lists all the files in a given directory, in a listbox. Is there a similar control in excel? Could I use this control in excel? I've got it but haven't tried it yet. regards Mark . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder and filenames?
Thanks for the reply, I might be able to use some of this
regards mark -----Original Message----- in addition to Bob's code.. These will bring up standard dialogs for opening and browsing files. see VBA help for details office.FileDialog excel.GetOpenfileName Private Sub Test() Dim xlapp As Object Set xlapp = GetObject(, "excel.application") xlapp.dialogs(475).Show '475 = xlDialogFileFind End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Mark" wrote: I want to list (from a known directory eg: E:\Customers) the folder names (that branch off the Customers folder) into sheet1 and filenames in each folder into sheet2. Can this be done with VBA or do I need to resort to an API call? If anyone knows or has an idea, let me know here and at my email: Alternatively, In vb6 there is a control that lists all the files in a given directory, in a listbox. Is there a similar control in excel? Could I use this control in excel? I've got it but haven't tried it yet. regards Mark . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder and filenames?
Hi,
Thanks for the reply, I will try it out but basically, there can be no userintervention except to say "go". See my reponse to Bob Phillips. regards mark -----Original Message----- Hi Mark Application.FileSearch will also do the job but partially: With Application.FileSearch ..NewSearch ..LookIn = "E:\Customers" ..SearchSubFolders = True ..FileType = msoFileTypeAllFiles 'see choice in list If .Execute() 0 Then Worksheets("Sheet1").Cells(1, 1).Value = "File Name" For i = 2 To .FoundFiles.Count Worksheets("Sheet1").Cells(i, 1).Value = .FoundFiles (i) Next i Else MsgBox "No files where found in the specified folder", vbInformation, "No Match" End If End With HTH Cordially Pascal "Mark" a écrit dans le message de ... I want to list (from a known directory eg: E:\Customers) the folder names (that branch off the Customers folder) into sheet1 and filenames in each folder into sheet2. Can this be done with VBA or do I need to resort to an API call? If anyone knows or has an idea, let me know here and at my email: Alternatively, In vb6 there is a control that lists all the files in a given directory, in a listbox. Is there a similar control in excel? Could I use this control in excel? I've got it but haven't tried it yet. regards Mark . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling pdf files from general folder to specific folder | Excel Discussion (Misc queries) | |||
how can i change my default working folder to a networked folder? | Excel Discussion (Misc queries) | |||
How to decide folder-depth or How to select more folders/subfolders (folder-tree) ? | Excel Discussion (Misc queries) | |||
Need code to save file to new folder, erase from old folder | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) |