Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
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 "Quco" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
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 -- Jim "Quco" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
Jim,
Could you also advise what the code would be if I wanted to include the files within any subfolders? Rob "Jim Rech" wrote in message ... 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 -- Jim "Quco" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
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 a 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
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 InsertModule Copy Jim's code and paste into this module Go back to the Excel sheet, View tabMacros dropdownViewHighlight 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 -- Regards Roger Govier "Quco" wrote in message ... 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 a 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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
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 -- Jim "RobN" wrote in message ... Jim, Could you also advise what the code would be if I wanted to include the files within any subfolders? Rob "Jim Rech" wrote in message ... 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 -- Jim "Quco" wrote in message ... 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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
Thanks Jim,
Very helpful! Rob "Jim Rech" wrote in message ... 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 -- Jim "RobN" wrote in message ... Jim, Could you also advise what the code would be if I wanted to include the files within any subfolders? Rob "Jim Rech" wrote in message ... 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 -- Jim "Quco" wrote in message ... 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? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
Roger,
It works!!! Thank you very much... this is exciting! I'm going to learn more about macros and visual basic. "Roger Govier" wrote: 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 InsertModule Copy Jim's code and paste into this module Go back to the Excel sheet, View tabMacros dropdownViewHighlight 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 -- Regards Roger Govier "Quco" wrote in message ... 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 a 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? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Importing Text files to Excel 2007
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. -- Regards Roger Govier "Quco" wrote in message ... Roger, It works!!! Thank you very much... this is exciting! I'm going to learn more about macros and visual basic. "Roger Govier" wrote: 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 InsertModule Copy Jim's code and paste into this module Go back to the Excel sheet, View tabMacros dropdownViewHighlight 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 -- Regards Roger Govier "Quco" wrote in message ... 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 a 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing text-files | Excel Discussion (Misc queries) | |||
Excel - Importing Text Files | Excel Discussion (Misc queries) | |||
Importing multiple Text files into Excel 2003 | Excel Discussion (Misc queries) | |||
Importing text files to Excel with big numbers | Excel Worksheet Functions | |||
Importing text files into Excel | Excel Discussion (Misc queries) |