Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jul 2001
    Location
    Agoura Hills, California, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DOS DIR Command to Excel (VBA/Excel 2002/10.2614.2625)

    Is there a way I can get the DIR contents of the A: drive to just dump into a sheet in Excel? The registration process at the company I work for has a layer of QA that involves running a DIR command on the diskette and making sure everything is where it's supposed to be. I'd rather we had an Excel sheet that could return results for this. I have a batch file that runs the dir command and sends it to a ~dirtemp.txt file, so just hints about how to run this Batch from within Excel VBA and then copying and pasting the contents would be helpful too.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DOS DIR Command to Excel (VBA/Excel 2002/10.2614.2625)

    There is a Dir function in VBA that can be used to do essentially the same thing as the DOS DIR command. For example, the following code will put a list of all the files in C:Work into column A of the active sheet.

    <pre>Public Sub ListFiles()
    Dim I As Long
    Dim strFileName As String
    I = 0
    strFileName = Dir("C:Work*.*")
    While strFileName <> ""
    ActiveSheet.Range("A1").Offset(I, 0).Value = strFileName
    I = I + 1
    strFileName = Dir()
    Wend
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Jul 2001
    Location
    Agoura Hills, California, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DOS DIR Command to Excel (VBA/Excel 2002/10.2614.2625)

    Thank you Legare. Here's another stinker for you then. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    How would I get the created date for those files. Is this pollable information?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DOS DIR Command to Excel (VBA/Excel 2002/10.2614.2625)

    The FileDateTime function will give you the date and time the file was last modified. From the Help file:

    <hr>
    FileDateTime Function


    Returns a Variant (Date) that indicates the date and time when a file was created or last modified.

    Syntax

    FileDateTime(pathname)

    The required pathname argument is a string expression that specifies a file name. The pathname may include the directory or folder, and the drive.
    <hr>
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DOS DIR Command to Excel (VBA/Excel 2002/10.2614.2625)

    You can use scripting to do this. You will need to set a reference to Microsoft Scripting Runtime:<pre>Dim sFilename As String
    Dim fs As filesystemObject
    Dim i As Integer
    Set fs = CreateObject("Scripting.FileSystemObject")

    sFilename = Dir("c:*.xls", vbNormal)
    Do While sFilename <> ""
    i = i + 1
    Cells(i, 1) = sFilename
    Cells(i, 2) = fs.GetFile("c:" & sFilename).DateLastModified
    sFilename = Dir
    Loop</pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •