Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
External File Paths
Hi there,
Since my previous tests failed - the file paths werent entered into the spreadsheet correctly!! I wondered if it were possible to have excel extract a list of all the files in a drive and put each file found's full path location in a row on the spreadsheet. for example - I have my C Drive. I would expect excel to find all Windows files and then list their full path down column A, Hope this explains what I'm looking for, Thanks in advance, Kind Regards, Stuart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
External File Paths
You might be able to do something with this code
Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook this.Worksheets.Add.Name = "FileList" sFolder = "C:\MyTest" '<=== change to suit If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Worksheets("FileList").Cells(cnt, "A").Value = file.Path cnt = cnt + 1 End If Next file End If ' sFolder < "" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stuart" wrote in message oups.com... Hi there, Since my previous tests failed - the file paths werent entered into the spreadsheet correctly!! I wondered if it were possible to have excel extract a list of all the files in a drive and put each file found's full path location in a row on the spreadsheet. for example - I have my C Drive. I would expect excel to find all Windows files and then list their full path down column A, Hope this explains what I'm looking for, Thanks in advance, Kind Regards, Stuart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
External File Paths
Bob,
Thank you for your repsonse.....greatly appreciated. It keeps debugging at the part of the code this.Worksheets.Add.Name = "FileList" Any ideas? I attempted the folder C:\temp and it didnt work. Thanks Bob, Stuart Bob Phillips wrote: You might be able to do something with this code Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook this.Worksheets.Add.Name = "FileList" sFolder = "C:\MyTest" '<=== change to suit If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Worksheets("FileList").Cells(cnt, "A").Value = file.Path cnt = cnt + 1 End If Next file End If ' sFolder < "" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stuart" wrote in message oups.com... Hi there, Since my previous tests failed - the file paths werent entered into the spreadsheet correctly!! I wondered if it were possible to have excel extract a list of all the files in a drive and put each file found's full path location in a row on the spreadsheet. for example - I have my C Drive. I would expect excel to find all Windows files and then list their full path down column A, Hope this explains what I'm looking for, Thanks in advance, Kind Regards, Stuart |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
External File Paths
Got that sorted Bob,
Thanks anything I can do to get that code to bring in the directories too? Thanks Stuart Stuart wrote: Bob, Thank you for your repsonse.....greatly appreciated. It keeps debugging at the part of the code this.Worksheets.Add.Name = "FileList" Any ideas? I attempted the folder C:\temp and it didnt work. Thanks Bob, Stuart Bob Phillips wrote: You might be able to do something with this code Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook this.Worksheets.Add.Name = "FileList" sFolder = "C:\MyTest" '<=== change to suit If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Worksheets("FileList").Cells(cnt, "A").Value = file.Path cnt = cnt + 1 End If Next file End If ' sFolder < "" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stuart" wrote in message oups.com... Hi there, Since my previous tests failed - the file paths werent entered into the spreadsheet correctly!! I wondered if it were possible to have excel extract a list of all the files in a drive and put each file found's full path location in a row on the spreadsheet. for example - I have my C Drive. I would expect excel to find all Windows files and then list their full path down column A, Hope this explains what I'm looking for, Thanks in advance, Kind Regards, Stuart |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
External File Paths
You mean like this
Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook this.Worksheets.Add.Name = "FileList" sFolder = "C:\Data1" '<=== change to suit If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Worksheets("FileList").Cells(cnt, "A").Value = file.Path cnt = cnt + 1 End If Next file For Each fldr In Folder.SubFolders Worksheets("FileList").Cells(cnt, "A").Value = fldr.Path cnt = cnt + 1 Next End If ' sFolder < "" End Sub or are you looking to recurse throught the whole subdirectory structure listing files? http://support.microsoft.com/kb/185476/EN-US/ How To Search Directories to Find or List Files http://support.microsoft.com/kb/185601/EN-US/ HOW TO: Recursively Search Directories by Using FileSystemObject http://support.microsoft.com/kb/186118/EN-US/ How To Use FileSystemObject with Visual Basic -- Regards, Tom Ogilvy "Stuart" wrote: Got that sorted Bob, Thanks anything I can do to get that code to bring in the directories too? Thanks Stuart Stuart wrote: Bob, Thank you for your repsonse.....greatly appreciated. It keeps debugging at the part of the code this.Worksheets.Add.Name = "FileList" Any ideas? I attempted the folder C:\temp and it didnt work. Thanks Bob, Stuart Bob Phillips wrote: You might be able to do something with this code Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook this.Worksheets.Add.Name = "FileList" sFolder = "C:\MyTest" '<=== change to suit If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Worksheets("FileList").Cells(cnt, "A").Value = file.Path cnt = cnt + 1 End If Next file End If ' sFolder < "" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stuart" wrote in message oups.com... Hi there, Since my previous tests failed - the file paths werent entered into the spreadsheet correctly!! I wondered if it were possible to have excel extract a list of all the files in a drive and put each file found's full path location in a row on the spreadsheet. for example - I have my C Drive. I would expect excel to find all Windows files and then list their full path down column A, Hope this explains what I'm looking for, Thanks in advance, Kind Regards, Stuart |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
External File Paths
Hi Tom,
I would like excel to list the files and folder is C Drive, then list the files and folders within the subfolders found right through until every file and folder on the drive is on excel. Can this be done? Many thanks for your previous advice. Stuart Tom Ogilvy wrote: You mean like this Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook this.Worksheets.Add.Name = "FileList" sFolder = "C:\Data1" '<=== change to suit If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Worksheets("FileList").Cells(cnt, "A").Value = file.Path cnt = cnt + 1 End If Next file For Each fldr In Folder.SubFolders Worksheets("FileList").Cells(cnt, "A").Value = fldr.Path cnt = cnt + 1 Next End If ' sFolder < "" End Sub or are you looking to recurse throught the whole subdirectory structure listing files? http://support.microsoft.com/kb/185476/EN-US/ How To Search Directories to Find or List Files http://support.microsoft.com/kb/185601/EN-US/ HOW TO: Recursively Search Directories by Using FileSystemObject http://support.microsoft.com/kb/186118/EN-US/ How To Use FileSystemObject with Visual Basic -- Regards, Tom Ogilvy "Stuart" wrote: Got that sorted Bob, Thanks anything I can do to get that code to bring in the directories too? Thanks Stuart Stuart wrote: Bob, Thank you for your repsonse.....greatly appreciated. It keeps debugging at the part of the code this.Worksheets.Add.Name = "FileList" Any ideas? I attempted the folder C:\temp and it didnt work. Thanks Bob, Stuart Bob Phillips wrote: You might be able to do something with this code Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook this.Worksheets.Add.Name = "FileList" sFolder = "C:\MyTest" '<=== change to suit If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Worksheets("FileList").Cells(cnt, "A").Value = file.Path cnt = cnt + 1 End If Next file End If ' sFolder < "" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stuart" wrote in message oups.com... Hi there, Since my previous tests failed - the file paths werent entered into the spreadsheet correctly!! I wondered if it were possible to have excel extract a list of all the files in a drive and put each file found's full path location in a row on the spreadsheet. for example - I have my C Drive. I would expect excel to find all Windows files and then list their full path down column A, Hope this explains what I'm looking for, Thanks in advance, Kind Regards, Stuart |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
External File Paths
Option Explicit
Private cnt As Long Private FSO As Object Sub Folders() Dim i As Long Dim sFolder As String Dim iStart As Long Dim iEnd As Long Dim fOutline As Boolean Set FSO = CreateObject("Scripting.FileSystemObject") cnt = 0 ActiveWorkbook.Worksheets.Add.Name = "FileList" sFolder = "C:\MyTest" '<=== change to suit ReDim arfiles(2, 0) SelectFiles sFolder End Sub '----------------------------------------------------------------------- Sub SelectFiles(sPath As String) '----------------------------------------------------------------------- Dim oSubFolder As Object Dim oFolder As Object Dim oFile As Object Dim oFiles As Object Set oFolder = FSO.GetFolder(sPath) Set oFiles = oFolder.Files For Each oFile In oFiles cnt = cnt + 1 Worksheets("FileList").Cells(cnt, "A").Value = oFile.Path Next oFile For Each oSubFolder In oFolder.Subfolders SelectFiles oSubFolder.Path Next End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stuart" wrote in message oups.com... Hi Tom, I would like excel to list the files and folder is C Drive, then list the files and folders within the subfolders found right through until every file and folder on the drive is on excel. Can this be done? Many thanks for your previous advice. Stuart Tom Ogilvy wrote: You mean like this Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook this.Worksheets.Add.Name = "FileList" sFolder = "C:\Data1" '<=== change to suit If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Worksheets("FileList").Cells(cnt, "A").Value = file.Path cnt = cnt + 1 End If Next file For Each fldr In Folder.SubFolders Worksheets("FileList").Cells(cnt, "A").Value = fldr.Path cnt = cnt + 1 Next End If ' sFolder < "" End Sub or are you looking to recurse throught the whole subdirectory structure listing files? http://support.microsoft.com/kb/185476/EN-US/ How To Search Directories to Find or List Files http://support.microsoft.com/kb/185601/EN-US/ HOW TO: Recursively Search Directories by Using FileSystemObject http://support.microsoft.com/kb/186118/EN-US/ How To Use FileSystemObject with Visual Basic -- Regards, Tom Ogilvy "Stuart" wrote: Got that sorted Bob, Thanks anything I can do to get that code to bring in the directories too? Thanks Stuart Stuart wrote: Bob, Thank you for your repsonse.....greatly appreciated. It keeps debugging at the part of the code this.Worksheets.Add.Name = "FileList" Any ideas? I attempted the folder C:\temp and it didnt work. Thanks Bob, Stuart Bob Phillips wrote: You might be able to do something with this code Dim FSO As Object Sub ProcessFiles() Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim cnt As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook this.Worksheets.Add.Name = "FileList" sFolder = "C:\MyTest" '<=== change to suit If sFolder < "" Then Set Folder = FSO.GetFolder(sFolder) Set Files = Folder.Files cnt = 1 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then Worksheets("FileList").Cells(cnt, "A").Value = file.Path cnt = cnt + 1 End If Next file End If ' sFolder < "" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stuart" wrote in message oups.com... Hi there, Since my previous tests failed - the file paths werent entered into the spreadsheet correctly!! I wondered if it were possible to have excel extract a list of all the files in a drive and put each file found's full path location in a row on the spreadsheet. for example - I have my C Drive. I would expect excel to find all Windows files and then list their full path down column A, Hope this explains what I'm looking for, Thanks in advance, Kind Regards, Stuart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make excel check external file paths | Excel Programming | |||
excel file paths | Excel Discussion (Misc queries) | |||
Using Variables for file paths in QueryTables.Add | Excel Programming | |||
file paths | Excel Discussion (Misc queries) | |||
Relative paths to external data? | Excel Discussion (Misc queries) |