How to change a file directory to get .txt document of file contents?

davidplowman1

New Member
Joined
Nov 21, 2014
Messages
2
Hello:

I’m working on a project that will ultimately get put into an Excel Spreadsheet, but my question is regarding a preceding step, and hopefully finding a better, more effecting way to complete what’s now a manual, copy-and-paste process.

The spreadsheet is in many ways going to be an “index” of a lot of different information that is kept on our company’s network drive.
As such, one column will list the file name. The current process is to have the network drive window open, click the “rename” button to highlight the file and manually copy-and-paste the file name into the corresponding Excel column.

I’ve done some research, and have learned how to use a command-prompt to create a text document that will create a text document that lists the contents of a drive. From there, I can dump to information into Excel and clean it up using text-to-columns. (The command is “dir>List.txt)
My problem is, I that command only maps the current drive (the c drive?) and not the drive I want.

Can tell me how to change the network drive using the correct windows command prompts? (Please be very step-by-step in your replies, I haven’t used command prompts since the pre-windows days in the early 80’s, and even then I was using Apple computers and BASIC, so this “back to the future” exercise is really freaking me out. :LOL::LOL: )

Thank you,
David
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
I’ve done some research, and have learned how to use a command-prompt to create a text document that will create a text document that lists the contents of a drive. From there, I can dump to information into Excel and clean it up using text-to-columns. (The command is “dir>List.txt)
My problem is, I that command only maps the current drive (the c drive?) and not the drive I want.
Did you either navigate to the drive you want to list before using Dir, or at least specify it as part of the Dir parameter? Pretty basic stuff...

See also: How to add the Print Directory feature for folders in Windows XP, in Windows Vista, in Windows 7
 
Upvote 0
Paul:

Sorry, it just doesn't seem basic to me, since I've never done this before. I did click the link you provided. Seeing the phrase "however, serious problems might occur if you... “ did little to assuage my fears. :)I know Excel well, but not the first thing about working with command prompts and certainly don’t want to inadvertently do anything that could mess up my company’s computers.

Is there a simple way to change the directory so I can get a text printout of the file listing on a network drive? If so, what easy-to-follow steps can I follow? I’d prefer steps that do not have the potential of causing serious damage if I don’t follow the steps properly. :)

Thank you,
David
 
Upvote 0
I did click the link you provided. Seeing the phrase "however, serious problems might occur if you... “ did little to assuage my fears.
You won't have such issues to worry about if you run the 'FixIt'. The risks to which the warnings apply only concern editing the Registry yourself.
Is there a simple way to change the directory so I can get a text printout of the file listing on a network drive? If so, what easy-to-follow steps can I follow?
As I indicated, simply changing to the folder concerned before running the Dir command, or specifying the folder as part of the Dir command is all that's required. For example, if the folder is:
I:\SomeFolder
for the command prompt you could use either:
Dir I:\SomeFolder > FileList.Txt
or:
CD I:\SomeFolder
Dir > FileList.Txt

The first one will output the list in the current folder, whilst the second one will output it in I:\SomeFolder (because that's now the current folder)

Similarly, you can run the command from one folder and send the output to another. For example:
Dir I:\SomeFolder > C:\Users\David\Documents\FileList.txt
will output the FileList.txt of the I:\SomeFolder contents in the C:\Users\David\Documents folder.
 
Upvote 0
Copy the Code to a Excel Module

There are two Macros (subs) , ImportFolderInfo and ImportFileInfo

The macros create a sheet and import Folder Information or File Information of the selected Directory or Drive to it



Code:
Sub ImportFolderInfo()
    
    
If ActiveSheet Is Nothing Then
  MsgBox "There is no Active Workbook !!!"
  Exit Sub
End If
    
    
Dim DirPath, FileSaveName

FileSaveName = Application.GetSaveAsFilename( _
               "Select This Folder", _
               Title:="Open The Folder And Click The [Save] Button")
    
If FileSaveName <> False Then

   DirPath = Left(FileSaveName, Len(FileSaveName) - 19)
   shPATH = DirPath
   
   ''shPATH = "C:\"
   PathSep = 0
   
   For i = (Len(DirPath) - 1) To 1 Step -1
       If Mid(DirPath, i, 1) = Application.PathSeparator Then
          PathSep = 1
          DirName = Mid(DirPath, i + 1, Len(DirPath))
          'ShName = "DIRECTORY_INFO" + Mid(DirName, 1, Len(DirName) - 1)
          ShName = "DIRECTORY_INFO"
       Exit For
       End If
   Next
   
   If PathSep = 0 Then
      DirName = Left(DirPath, Len(DirPath) - 1)
      ShName = "ROOT " + Mid(DirName, 1, Len(DirName) - 1)
   End If
      
   Dim fs, f, ffile, fc
   Set fs = CreateObject("Scripting.FileSystemObject")
   Set f = fs.GetFolder(DirPath)
   Set fc = f.SubFolders
      
Dim No
PAVLA = ""
On Error GoTo SheetNameError
Set NewSheet = Worksheets.Add

NextName:
NewSheet.Name = ShName & PAVLA & No


   No = 1
   Cells(1, 1) = "A/A"
   Cells(1, 2) = "DIRECTORY"
   Cells(1, 3) = "SIZE"
   Cells(1, 4) = "EXT"
   Cells(1, 5) = "ATTRIBUTES"
   Cells(1, 6) = "FILES"
   Cells(1, 7) = "sDIRS"
   Cells(1, 8) = "CREATED"
   Cells(1, 9) = "ACCESSED"
   Cells(1, 10) = "MODIFIED"
     
   
   On Error Resume Next
   For Each ffile In fc
       No = No + 1
       Cells(No, 1) = No - 1
       Cells(No, 2) = ffile.Name
       Cells(No, 3) = ffile.Size
       Cells(No, 4) = fs.GetExtensionName(ffile)
       Cells(No, 5) = FindFileAttributes(ffile)
       Cells(No, 6) = ffile.Files.Count + Files_Count(ffile.Path)
       Cells(No, 7) = ffile.SubFolders.Count + Folder_Count(ffile.Path)
       Cells(No, 8) = ffile.DateCreated
       Cells(No, 9) = ffile.DateLastAccessed
       Cells(No, 10) = ffile.dateLastModified
       
   Next
   Range("A1:J1").Font.Bold = True
   Range("A1:J1").HorizontalAlignment = xlCenter
   
   Range(Cells(1, 2), Cells(No, 9)).Select
 Selection.Sort _
     Key1:=Range("B2"), Order1:=xlAscending, _
     Header:=xlGuess, OrderCustom:=1, _
     MatchCase:=False, Orientation:=xlTopToBottom
   
   Cells.EntireColumn.AutoFit
   Range(Cells(1, 1), Cells(No, 10)).Select
   Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
   Selection.Borders(xlEdgeLeft).Weight = xlThin
   Selection.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
   Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
   Selection.Borders(xlEdgeTop).Weight = xlThin
   Selection.Borders(xlEdgeTop).ColorIndex = xlAutomatic
   Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
   Selection.Borders(xlEdgeBottom).Weight = xlThin
   Selection.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
   Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
   Selection.Borders(xlEdgeRight).Weight = xlThin
   Selection.Borders(xlEdgeRight).ColorIndex = xlAutomatic
   Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
   Selection.Borders(xlInsideVertical).Weight = xlThin
   Selection.Borders(xlInsideVertical).ColorIndex = xlAutomatic
   Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
   Selection.Borders(xlInsideHorizontal).Weight = xlThin
   Selection.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
   Range(Cells(2, 3), Cells(No, 3)).NumberFormat = "#,##0"
   Range(Cells(2, 6), Cells(No, 6)).NumberFormat = "#,##"
   
   ActiveWindow.DisplayGridlines = False
   Range("A1").Select
   Selection.Activate
   
End If

Exit Sub
SheetNameError:
PAVLA = " -"
 No = No + 1
 Resume
 GoTo NextName

End Sub
Sub ImportFileInfo()
    
    
If ActiveSheet Is Nothing Then
  MsgBox "There is no Active Workbook !!!"
  Exit Sub
End If
    
    
Dim DirPath, FileSaveName

FileSaveName = Application.GetSaveAsFilename( _
               "Select This Folder", _
               Title:="Open The Folder And Click The [Save] Button")
    
If FileSaveName <> False Then

   DirPath = Left(FileSaveName, Len(FileSaveName) - 19)
   shPATH = DirPath
   PathSep = 0
   
   For i = (Len(DirPath) - 1) To 1 Step -1
       If Mid(DirPath, i, 1) = Application.PathSeparator Then
          PathSep = 1
          DirName = Mid(DirPath, i + 1, Len(DirPath))
          ShName = "FILE_INFO"
       Exit For
       End If
   Next
   
   If PathSep = 0 Then
      DirName = Left(DirPath, Len(DirPath) - 1)
      ShName = "ROOT " + Mid(DirName, 1, Len(DirName) - 1) + " Files"
   End If
      

   Dim fs, f, fc, ffile
   Set fs = CreateObject("Scripting.FileSystemObject")
   Set f = fs.GetFolder(DirPath)
   Set fc = f.SubFolders

Dim No
PAVLA = ""
On Error GoTo SheetNameError
Set NewSheet = Worksheets.Add

NextName:
NewSheet.Name = ShName & PAVLA & No

 On Error Resume Next
  
   Cells(1, 1) = "A/A"
   Cells(1, 2) = "FILENAME"
   Cells(1, 3) = "SIZE"
   Cells(1, 4) = "EXT"
   Cells(1, 5) = "TYPE"
   Cells(1, 6) = "ATTRIBUTES"
   Cells(1, 7) = "CREATED"
   Cells(1, 8) = "ACCESSED"
   Cells(1, 9) = "MODIFIED"
     
   No = 1
   For Each ffile In f.Files
       No = No + 1
       Cells(No, 1) = No - 1
       Cells(No, 2) = ffile.Name
       Cells(No, 3) = ffile.Size
       Cells(No, 4) = fs.GetExtensionName(ffile)
       Cells(No, 5) = ffile.Type
       Cells(No, 6) = FindFileAttributes(ffile)
       Cells(No, 7) = ffile.DateCreated
       Cells(No, 8) = ffile.DateLastAccessed
       Cells(No, 9) = ffile.dateLastModified
      
      
   Next
   
   Range("A1:I1").Font.Bold = True
   Range("A1:I1").HorizontalAlignment = xlCenter
   
   Range(Cells(1, 2), Cells(No, 9)).Select
 Selection.Sort _
     Key1:=Range("D2"), Order1:=xlAscending, _
     Key2:=Range("B2"), Order1:=xlAscending, _
     Header:=xlGuess, OrderCustom:=1, _
     MatchCase:=False, Orientation:=xlTopToBottom
   
   Cells.EntireColumn.AutoFit
   Range(Cells(1, 1), Cells(No, 9)).Select
   Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
   Selection.Borders(xlEdgeLeft).Weight = xlThin
   Selection.Borders(xlEdgeLeft).ColorIndex = xlAutomatic
   Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
   Selection.Borders(xlEdgeTop).Weight = xlThin
   Selection.Borders(xlEdgeTop).ColorIndex = xlAutomatic
   Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
   Selection.Borders(xlEdgeBottom).Weight = xlThin
   Selection.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
   Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
   Selection.Borders(xlEdgeRight).Weight = xlThin
   Selection.Borders(xlEdgeRight).ColorIndex = xlAutomatic
   Selection.Borders(xlInsideVertical).LineStyle = xlContinuous
   Selection.Borders(xlInsideVertical).Weight = xlThin
   Selection.Borders(xlInsideVertical).ColorIndex = xlAutomatic
   Selection.Borders(xlInsideHorizontal).LineStyle = xlContinuous
   Selection.Borders(xlInsideHorizontal).Weight = xlThin
   Selection.Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
   Range(Cells(2, 3), Cells(No, 3)).NumberFormat = "#,##0"
   
   ActiveWindow.DisplayGridlines = False
   Range("A1").Select
   Selection.Activate
   
End If

Exit Sub
SheetNameError:
PAVLA = " -"
 No = No + 1
 Resume
 GoTo NextName

End Sub
Sub AddAttribute(HasAttribute, ByRef fAttr As String, ByVal sAttribute As String)
     If HasAttribute <> 0 Then fAttr = fAttr & sAttribute & ", "
End Sub
Function FindFileAttributes(cfile)
 Dim fAttr As String
 AddAttribute cfile.Attributes And 16, fAttr, "Dir"
 AddAttribute cfile.Attributes And 8, fAttr, "Vol"
 AddAttribute cfile.Attributes And 0, fAttr, "Nor"
 AddAttribute cfile.Attributes And 1, fAttr, "Read"
 AddAttribute cfile.Attributes And 2, fAttr, "Hid"
 AddAttribute cfile.Attributes And 4, fAttr, "Sys"
 AddAttribute cfile.Attributes And 32, fAttr, "Arc"
 AddAttribute cfile.Attributes And 64, fAttr, "Alias"
 AddAttribute cfile.Attributes And 128, fAttr, "Com"
 
 If fAttr <> "" Then FindFileAttributes = Left(fAttr, Len(fAttr) - 2)
End Function

Function Files_Count(SearchDirPath) As Long

   On Error Resume Next
   
   Dim FileSystem, sFolder, sSubFolder, sItem
   Set FileSystem = CreateObject("Scripting.FileSystemObject")
   Set sFolder = FileSystem.GetFolder(SearchDirPath)
   Set sSubFolder = sFolder.SubFolders
   
   For Each sItem In sSubFolder
       
       Files_Count = Files_Count + sItem.Files.Count
       Files_Count = Files_Count + Files_Count(sItem.Path)
       
   Next

End Function

Function Folder_Count(SearchDirPath) As Long

   On Error Resume Next
   
   Dim FileSystem, sFolder, sSubFolder, sItem
   Set FileSystem = CreateObject("Scripting.FileSystemObject")
   Set sFolder = FileSystem.GetFolder(SearchDirPath)
   Set sSubFolder = sFolder.SubFolders
   
   For Each sItem In sSubFolder
       Folder_Count = Folder_Count + sItem.SubFolders.Count
       Folder_Count = Folder_Count + Folder_Count(sItem.Path)
       
   Next

End Function
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top