DOS from VBA

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • mailsatk
    New Member
    • Oct 2006
    • 4

    #1

    DOS from VBA

    Hi,

    I want to do exactly what has been stated by this person (Alan - see link below), just that I am using MS Excel instead of MS-Access.


    Can anyone tell me how the reply#5 would have helped? It seems he got it to work with that option.

    Shell "cmd.exe /c <yourcommand> "
    I am trying the following code -

    Code:
    Private Sub CommandButton1_Click()
    Dim x
    Dim y
    Dim z As String
    
    z = "dir E:\Satish /a:d /b /s>dirs.txt "
    
    x = Shell("D:\windows\system32\cmd.exe", 1) 'open command prompt
    y = Shell("cmd.exe/c z") 'execute the DOS command to write to the file dirs.txt
    
    End Sub
    But I get a runtime error on the line "y = Shell...."

    Maybe a very basic question, but I do not know how it will work :-(

    Any help will be greatly appreciated.

    Thanks
    Satish
  • willakawill
    Top Contributor
    • Oct 2006
    • 1646

    #2
    Originally posted by mailsatk
    y = Shell("cmd.exe/c z") 'execute the DOS command to write to the file dirs.txt
    You seem to have enclosed the variable z in a literal string which it is not a variable but just the text character z

    Comment

    • mailsatk
      New Member
      • Oct 2006
      • 4

      #3
      thanks willakawill.

      I tried the following piece of code now:
      Code:
      Private Sub CommandButton1_Click()
      
      Dim x
      Dim y
      Dim z As String
      
      z = "dir E:\Satish /a:d /b>dirs.txt"
      
      y = Shell("cmd.exe / c" & z) 'open the DOS prompt and execute the DOS command to write to the file dirs.txt
      
      End Sub
      This opens the command prompt for me, but it does not do anything apart from that. I want all the folders present under the directory "E:\Satish" to be written to a file dirs.txt.

      The DOS command "dir E:\Satish /a:d /b>dirs.txt" works just fine when executed directly, but I can't seem to get it work through the macro.

      regards,
      -Satish

      Originally posted by willakawill
      You seem to have enclosed the variable z in a literal string which it is not a variable but just the text character z

      Comment

      • willakawill
        Top Contributor
        • Oct 2006
        • 1646

        #4
        Originally posted by mailsatk
        thanks willakawill.

        I tried the following piece of code now:
        Code:
        Private Sub CommandButton1_Click()
        
        Dim x
        Dim y
        Dim z As String
        
        z = "dir E:\Satish /a:d /b>dirs.txt"
        
        y = Shell("cmd.exe / c" & z) 'open the DOS prompt and execute the DOS command to write to the file dirs.txt
        
        End Sub
        This opens the command prompt for me, but it does not do anything apart from that. I want all the folders present under the directory "E:\Satish" to be written to a file dirs.txt.

        The DOS command "dir E:\Satish /a:d /b>dirs.txt" works just fine when executed directly, but I can't seem to get it work through the macro.

        regards,
        -Satish
        This DOS string does not do anything on my computer

        Comment

        Working...