Importing Text files to Excel 2007

[Guest]

I'm being asked to test 5% of the files in a hard drive given to us.
There are 1620 files, so I need to test 81 randomnly choosen files.

I open the hard drive using Windows Explorer and sort them by Name.

I need to know if we can create a text file of the contents of the hard
drive, so that I can import it in Excel 2007 and place checkmarks on an
adjacent column to keep track of what files I'm testing. How do I do that? It
is possible? How?

 

[Guest]

from the command prompt (file/run/cmd)
cd\
dir /s > files.txt
it will make a text file called files.txt with all files on the drive.
you can do dir/? to get different options for the dir command

 

[Guest]

Marvin,

Back in 1986 I learned a little bit of MS-DOS commands with an Atari XT
computer I used just for fun, but I can't remember how to use these commands
now.

This is what I have done so far:

1. File > Run > cmd
2. Enter G: to switch to the location of the hard drive

Now, the actual path for the folder containing all these files is:
G:\Gulf

Unfortunately I can't get in the Gulf folder to try the command "dir/s >
files.txt"
I do not understood the rest of your instructions. Could you please clarify?

 

[Jim Rech]

You can get a quick list of the files in a folder with this macro. Put the
folder path in cell A1.

Sub a()
Dim FilePath As String
Dim FName As String
Dim Counter As Integer
FilePath = Range("A1").Value
If Right(FilePath, 1) <> "\" Then FilePath = FilePath & "\"
FName = Dir(FilePath & "*.*", vbNormal)
Do While FName <> ""
Counter = Counter + 1
Cells(Counter + 1, 1).Value = FName
FName = Dir
Loop
End Sub

 

[RobN]

Jim,

Could you also advise what the code would be if I wanted to include the
files within any subfolders?

Rob

 

[Guest]

This forum/discussion is creating more questions each time I read it. It
seems like it's limited to computer programmers? anyway, thanks to all for
your inputs. My new questions are:

1. What is a Macro?
2. How do I run it using Excel 2007?
3. Should I replace "FilePath" for "G:\Gulf" in that code? (I did it, then
save the Excel spreadsheet and open it again, but nothing hapens).
4. Where can I learn more about writing codes to use in Excel?

 

[Roger Govier]

Hi

As Jim said at the beginning of his post, put your Path in cell A1 of
the sheet.
A1 G1\Gulf

As for where you enter the code, Right click on the sheet tab.
Choose View Code, which will open up the Visual Basic Editor.
Choose Insert>Module
Copy Jim's code and paste into this module
Go back to the Excel sheet, View tab>Macros dropdown>View>Highlight
macro "a">Run

For more information on getting started with macros then

David McRitchie has lots of useful help on his site at
http://www.mvps.org/dmcritchie/excel/install.htm
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Jim Rech]

Here's two ways to drill into subfolders:

1. Requires a reference to MS Scriptiong Runtime:

Option Compare Text

Sub ListXLFiles()
Dim FilesCollection As New Collection, Counter As Long
FindFiles "c:\excel", "*.xls", FilesCollection, True
Sheet1.Columns(1).Clear
For Counter = 1 To FilesCollection.Count
Sheet1.Cells(Counter, 1).Value = FilesCollection(Counter)
Next
End Sub

Sub FindFiles(FolderName As String, FileSpec As String, Col As Collection,
Recurs As Boolean)
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fldSub As Scripting.Folder
Dim fle As Scripting.file
Set fso = New Scripting.FileSystemObject
Set fld = fso.GetFolder(FolderName)
For Each fle In fld.Files
If fle.Name Like FileSpec Then Col.Add fle.Path
Next
If Recurs Then
For Each fldSub In fld.SubFolders
FindFiles fldSub.Path, FileSpec, Col, True
Next
End If
Set fso = Nothing
End Sub

2. Uses FileSearch which I believe is not in Excel 2007.

Sub FileSearchList()
Dim i As Integer
Sheet1.Columns(2).Clear
With Application.FileSearch
.NewSearch
.LookIn = "c:\excel"
.SearchSubFolders = True
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Sheet1.Cells(i, 2).Value = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

 

[RobN]

Thanks Jim,

Very helpful!

Rob

Here's two ways to drill into subfolders:

1. Requires a reference to MS Scriptiong Runtime:

Option Compare Text

Sub ListXLFiles()
Dim FilesCollection As New Collection, Counter As Long
FindFiles "c:\excel", "*.xls", FilesCollection, True
Sheet1.Columns(1).Clear
For Counter = 1 To FilesCollection.Count
Sheet1.Cells(Counter, 1).Value = FilesCollection(Counter)
Next
End Sub

Sub FindFiles(FolderName As String, FileSpec As String, Col As Collection,
Recurs As Boolean)
Dim fso As Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fldSub As Scripting.Folder
Dim fle As Scripting.file
Set fso = New Scripting.FileSystemObject
Set fld = fso.GetFolder(FolderName)
For Each fle In fld.Files
If fle.Name Like FileSpec Then Col.Add fle.Path
Next
If Recurs Then
For Each fldSub In fld.SubFolders
FindFiles fldSub.Path, FileSpec, Col, True
Next
End If
Set fso = Nothing
End Sub

2. Uses FileSearch which I believe is not in Excel 2007.

Sub FileSearchList()
Dim i As Integer
Sheet1.Columns(2).Clear
With Application.FileSearch
.NewSearch
.LookIn = "c:\excel"
.SearchSubFolders = True
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Sheet1.Cells(i, 2).Value = .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

 

[Guest]

Roger,

It works!!! Thank you very much... this is exciting! I'm going to learn more
about macros and visual basic.

 

[Roger Govier]

Hi

The thanks is entirely due to Jim, and i am sure he will be pleased to
know that you got it working.
Thank you for posting back to let us know that it solved the problem.