Running commands in and reading from the command line - possible?

So let's start with a giant pile of files that need to be renamed that have unpredictable filenames and file extensions.

Let's say I can pull a report in excel format out of the system that generated those files. It will tell me the (unique) first five characters of the unpredictable filename and includes some other data that I'm putting in the filename to make it human-readable.

I can, and already do, use a series of vlookups and concatenate functions to generate command line "ren" commands to get sensible filenames. (e.g. ="ren """&A1&"."&B1&""" """&A1&" - "&C1&" - "&D1&"."&B1&"""" yields a value of ren "asdfg.doc" "asdfg - file #10 - the quick brown fox.doc") which I then copy back into the command line. But I'd like to automate it further if possible.

Problem #1: Can I open a command line at the location of the active workbook, cd into a subdirectory, run a dir /b, and feed the results back into column A of the active workbook?
Problem #2: Can I automatically send the previously discussed ren commands back to the command line?

Thanks in advance

Hi Shanyn,

See the file associated with post #2 in the following thread. It creates a list of file using the dir /b command and can retrieve the results in a separate Excel Sheet. http://www.excelforum.com/excel-prog...h-results.html

Lewis

Thanks very mcuh to both of you. These look like they will solve my problems brilliantly.

Hi Shanyn,

See the following workbook which contains the code that follows this text. It simplifies the code in the link I provided above so it should be easier for you to follow the code. See Sheet 'Annotated' and the code module 'modAnnotated'.

Note that some Utility routines are not listed below, but are in the attached file.

Lewis

Option Explicit

Sub FindExcelFilesInFolderOnly()

  Dim sPath As String
  Dim sSearchSpec As String
  Dim sSubFolderOption As String
  
  'Define the 'Search Specification' as all Excel Files  (with extensions of 'xls*')
  sSearchSpec = "*.xls*"
  
  'Search in this folder only (no option)
  sSubFolderOption = ""
  
  'Arbitrarily define the path as the folder that contains the file that is running the code
  sPath = ThisWorkbook.Path & "\"
  
  Call NewCreateAndRunScratch00DotBatFile(sSearchSpec, sSubFolderOption, sPath)
  
  MsgBox "Windows 'Dir' command may still be running." & vbCrLf & _
         "When the 'Dir' command has completed, output will be in:" & vbCrLf & _
         "Folder: " & sPath & vbCrLf & _
         "File:  Scratch00.txt"


End Sub


Sub FindExcelFilesInFolderAndAllSubFolders()

  Dim sPath As String
  Dim sSearchSpec As String
  Dim sSubFolderOption As String
  
  'Define the 'Search Specification' as all Excel Files  (with extensions of 'xls*')
  sSearchSpec = "*.xls*"
  
  'Search in all SubFolders (option '/s')
  sSubFolderOption = " /s "
  
  'Arbitrarily define the path as the folder that contains the file that is running the code
  sPath = ThisWorkbook.Path & "\"
  
  Call NewCreateAndRunScratch00DotBatFile(sSearchSpec, sSubFolderOption, sPath)
  
  MsgBox "Windows 'Dir' command may still be running." & vbCrLf & _
         "When the 'Dir' command has completed, output will be in:" & vbCrLf & _
         "Folder: " & sPath & vbCrLf & _
         "File:  Scratch00.txt"


End Sub

Sub ImportScratch00DotTextIntoSheet2()
  'This copies a 'Source Text File' to a 'Destination Sheet' in the Active Workbook
  'starting at cell 'A1'.
  '
  'NOTE: It is the responsibility of the calling routine to make sure that the text file
  '      does not cause a runtime error due to 'too many lines' or 'memory overflow'.
  
  Dim wbDestination As Workbook
  Dim wbSource As Workbook
  Dim wsDestination As Worksheet
  
  Dim sPath As String
  Dim sSourcePathAndFile As String
  
  'Define the path as the folder that contains the file that is running the code
  sPath = ThisWorkbook.Path & "\"
  
  'Generate the Source File Name
  sSourcePathAndFile = sPath & "Scratch00.txt"
  
  'Set the Destination objects
  Set wbDestination = ThisWorkbook
  Set wsDestination = wbDestination.Sheets("Sheet2")
  
  'Clear the contents of the Destination Sheet
  wsDestination.Cells.Clear
  
  'Set the Source object
  Set wbSource = Workbooks.Open(sSourcePathAndFile)

  'Copy the entire 'Source Text file' to the destination sheet
  wbSource.Sheets(1).Cells.Copy wsDestination.Cells
    
  'Clear the Clipboard buffer (may not be needed - but used for safety)
  Application.CutCopyMode = False

  'Close the 'Source File'
  wbSource.Close SaveChanges:=False
  
  'Clear all the object references
  Set wbDestination = Nothing
  Set wsDestination = Nothing
  Set wbSource = Nothing
  
End Sub



Sub NewCreateAndRunScratch00DotBatFile(sSearchSpec As String, _
                                       sTraverseSubFoldersOption As String, _
                                       sScratchPath As String)
  'This creates text 'Scratch00.bat' that:
  'a. Runs the MS-DOS 'dir' command with output to file Scratch00.txt
  '
  'This .bat file uses the following files in the same folder:
  'a. Scratch00.bat - the file created by this routine
  'b. Scratch00.txt - the output of the 'dir' command is directed to this file
  '
  'The contents of the file are (without the leading 'single quotes') of the form:
  'dir c:\*.xls /s /b >c:\tmp\Scratch00.txt
  'exit
  '
  '
  'MS-DOS dir cmd
  '*.xls* (find all Excel files)
  '       can use *.* for ALL FILES (do not use * by itself)
  '       can specify a path too
  '/b = brief output (path and file name only)
  '/s = include all subdirectories
  '>x.txt put the output of the 'dir command' in file x.txt (can use full path and file name)
  '
  'e.g dir c:\*.xls /s /b >c:\tmp\x.txt
  
  
  Dim iError As Long
  Dim iFileNo As Integer
  
  Dim sCommandString As String
  Dim sDotBatPathAndFile As String
  Dim sScratch00DotTxtPathAndFile As String

  'Delete the files if they already exist
  Call LjmFileDelete(sScratchPath, sScratchFile00A_NAME)
  Call LjmFileDelete(sScratchPath, sScratchFile00B_NAME)

  'Create the full paths and file names
  sDotBatPathAndFile = sScratchPath & sScratchFile00A_NAME
  sScratch00DotTxtPathAndFile = """" & sScratchPath & sScratchFile00B_NAME & """"

  'Verify that the files don't exist
  If LJMFileExists(sDotBatPathAndFile) = True Then
    MsgBox "TERMINATING. Scratch File still exists." & vbCrLf & _
           "Folder: " & sScratchPath & vbCrLf & _
           "File Name: " & sScratchFile00A_NAME & vbCrLf & _
           "File SHOULD NOT EXIST."
    iError = 1
    Exit Sub
  End If

  If LJMFileExists(sScratch00DotTxtPathAndFile) = True Then
    MsgBox "TERMINATING. Scratch File still exists." & vbCrLf & _
           "Folder: " & sScratchPath & vbCrLf & _
           "File Name: " & sScratchFile00B_NAME & vbCrLf & _
           "File SHOULD NOT EXIST."
    iError = 1
    Exit Sub
  End If

  'Create the Command string for the 'dir' command
  sCommandString = "dir " & """" & sSearchSpec & """" & " /b " & sTraverseSubFoldersOption & " > " & sScratch00DotTxtPathAndFile
  
  'Create the file only if the file does not exist
  If (Dir(sDotBatPathAndFile) = "") Then
  
    'Allocate a file 'handle'
    iFileNo = FreeFile
  
    'Set error handler to close the file 'handle'
    On Error GoTo CLOSEFILE
  
    'Open the file for writing
    Open sDotBatPathAndFile For Output As #iFileNo
  
    'Write to the file
    Print #iFileNo, "@echo off"
    Print #iFileNo, "echo " & sDotBatPathAndFile; " created on " & Now()
    Print #iFileNo, sCommandString
    Print #iFileNo, "Exit"
  
    'Close the file
CLOSEFILE:
    Close #iFileNo
    On Error GoTo 0
  End If

  'Execute the .bat file
  'Multiple quotes are to allow use of files with embedded spaces
  Call LjmRunCmdDotExe("""" & sDotBatPathAndFile & """", "Close")  'Close cmd.exe

ERROR_EXIT:

End Sub
End Sub