# Run MS Dos Command CMD any copy text from Excel cells...help please?



## dwilson38550m (Aug 5, 2018)

Hi,

I am almost there but struggling with the last part - I have a command line in an Excel workbook (SHEET1 cells A1 to A10) - I would like to have a macro to open cmd (command in MS DOS) and then copy the text in cells A1 to A10 to the command (CMD) line.  So far I have 


Shell "cmd.exe /k dir c:\windows\system\*.exe", vbNormalFocus


which successfully opens up CMD (and shows C:\Users\david.wilson\Desktop>)    but I can't copy the text (from cells A1 to A10) into this.  Also C:\Users\david.wilson\Desktop>...is there any way I can specify C:\Users\david.wilson\Desktop\downloads>  (which is the name of the folder I want to download the data to)?

Thanks in advance - if you could I would be very grateful.

Regards,
David


----------



## John_w (Aug 5, 2018)

I'm not sure if this code will do what you want - it's not clear if the A1:A10 cells each contain a DOS command which you want to be executed one after the other, or they contain arguments to a single DOS command, or something else.

The code creates a DOS batch file (.bat) containing the command lines in A1:A10 and runs the batch file.


```
Public Sub Create_and_Run_DOS_Batch_File()

    Dim batchFile As String
    Dim cell As Range
    
    batchFile = ThisWorkbook.Path & "\DOS_commands.bat"
    Open batchFile For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    For Each cell In Worksheets("Sheet1").Range("A1:A10")
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , cell.Value
    Next
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
    Shell "cmd.exe /k " & Q(batchFile), vbNormalFocus
    
End Sub

Private Function Q(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function
```


----------



## dwilson38550m (Aug 5, 2018)

Perfects - this works.  Rather than a .bat what if I want to create .pdf files (cell A1 refers to a command to download a example1.pdf, cell A2, refers to a command to download a example2.pdf, cell A3 example3.pdf etc etc  Ultimately I want 10 separate .pdf files saved to a specified location.  Can this be done by modifying the code you have given me?  Thanks in advance.


----------



## John_w (Aug 5, 2018)

I don't quite follow you.  If A1 contains the command to download example1.pdf, doesn't running that command (via the .bat file) _create _example1.pdf?  If you are saying that example1.pdf is downloaded, but not to the correct location then add following line immediately after the Open line:


```
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "cd /d C:\path\to\your\download\folder"  'change to required folder path
```


----------



## dwilson38550m (Aug 6, 2018)

OK now works. Much appreciated...thanks to everyone for their help.


----------



## dwilson38550m (Dec 26, 2018)

*Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

Hi,

I have a macro programme that runs from Excel - the CMDTEST macro opens the command prompt and downloads various invoices and saves these to a dedicated folder    P:\gla-files\Accounting Folder - Finance Team\1.Credit Control\USA\Debtors Falling Overdue\Summary Overdues Generated  This has always worked in the past but for some reason it no longer opens the CMD prompt.  Now it breaks and shows  END  DEBUG at the line      

Open batchFile For Output As #1 

Is there anything anyone can suggest?  Thanks in advance.

Regards,
David
--------------------------------------------------------------------------------- 



```
Sub cmdtest()
'
' cmdtest Macro
'
    Dim batchFile As String
    Dim cell As Range
    
    batchFile = ThisWorkbook.Path & "\DOS_commands.bat"
    Open batchFile For Output As #1 
        Print #1 , "cd /d P:\gla-files\Accounting Folder - Finance Team\1.Credit Control\USA\Debtors Falling Overdue\Summary Overdues Generated"
    For Each cell In Worksheets("CURLCOMMLIST").Range("C3:C275")
        Print #1 , cell.Value
    Next
    Close #1 
    
    Shell "cmd.exe /k " & Q(batchFile), vbNormalFocus
    
End Sub
```


----------



## GR00007 (Dec 26, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

Did you change PC's?
ThisWorkbook.Path is the path that the workbook was opened from - is "\DOS_commands.bat" an existing file in that path?


----------



## Kenneth Hobson (Dec 26, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

I guess your Q() encapsulates your string with double quotes?  You can do that for your cd line's path or:

```
Sub cmdtest()
  '
  ' cmdtest Macro
  '
  Dim batchFile As String
  Dim cell As Range
  
  batchFile = ThisWorkbook.Path & "\DOS_commands.bat"
  Open batchFile For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
  Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , "cd /d ""P:\gla-files\Accounting Folder - Finance Team\1.Credit Control\USA\Debtors Falling Overdue\Summary Overdues Generated"""
  For Each cell In Worksheets("CURLCOMMLIST").Range("C3:C275")
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , cell.Value
    Next
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
  
  Shell "cmd /k " & """" & batchFile & """", vbNormalFocus
End Sub
```


----------



## dwilson38550m (Dec 26, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

Hi thanks, do I need to enter the file path in the """"  should that fix it ok?


----------



## dwilson38550m (Dec 26, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

Hi, yes existing doc on that file path. Used to work ok but stopped a few weeks back. No change of pc. Thanks


----------



## dwilson38550m (Aug 5, 2018)

Hi,

I am almost there but struggling with the last part - I have a command line in an Excel workbook (SHEET1 cells A1 to A10) - I would like to have a macro to open cmd (command in MS DOS) and then copy the text in cells A1 to A10 to the command (CMD) line.  So far I have 


Shell "cmd.exe /k dir c:\windows\system\*.exe", vbNormalFocus


which successfully opens up CMD (and shows C:\Users\david.wilson\Desktop>)    but I can't copy the text (from cells A1 to A10) into this.  Also C:\Users\david.wilson\Desktop>...is there any way I can specify C:\Users\david.wilson\Desktop\downloads>  (which is the name of the folder I want to download the data to)?

Thanks in advance - if you could I would be very grateful.

Regards,
David


----------



## Kenneth Hobson (Dec 26, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

batchfile is the path so no, don't add anything else between the """".


----------



## dwilson38550m (Dec 26, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

Hi,

Sorry this still doesn't work...it's very frustrating but I think it must be something simple.  I have a   DOS_commands.bat file saved in   P:\gla-files\Accounting Folder - Finance Team\1.Credit Control\USA\Debtors Falling Overdue    I have even tried entering

batchFile = "P:\gla-files\Accounting Folder - Finance Team\1.Credit Control\USA\Debtors Falling Overdue\DOS_commands.bat"

still nothing.

is there anything else you can suggest - strange it worked before then stopped?


----------



## dwilson38550m (Dec 26, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

Sorry I should have noted that there is a sub string underneath 
-----------------------------------------------------------------------------


```
Sub cmdtest()
'
' cmdtest Macro
'
Dim batchFile As String
Dim cell As Range

batchFile = ThisWorkbook.Path & "\DOS_commands.bat"
Open batchFile For Output As #1 
Print #1 , "cd /d P:\gla-files\Accounting Folder - Finance Team\1.Credit Control\USA\Debtors Falling Overdue\Summary Overdues Generated"
For Each cell In Worksheets("CURLCOMMLIST").Range("C3:C275")
Print #1 , cell.Value
Next
Close #1 

Shell "cmd.exe /k " & Q(batchFile), vbNormalFocus
    
End Sub
Private Function Q(text As String) As String
    Q = Chr(34) & text & Chr(34)
End Function
```


----------



## Kenneth Hobson (Dec 26, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

As I explained, use your Q() or add the double quotes as I did for both bat filename and the cd line:

```
Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL]  , "cd /d " & Q("P:\gla-files\Accounting Folder - Finance Team\1.Credit Control\USA\Debtors Falling Overdue\Summary Overdues Generated")
```

Please paste code between code tags. Click # icon on toolbar to add the tags.


----------



## Macropod (Dec 26, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*



dwilson38550m said:


> I have a macro programme that runs from Excel - the CMDTEST macro opens the command prompt and downloads various invoices and saves these to a dedicated folder    P:\gla-files\Accounting Folder - Finance Team\1.Credit Control\USA\Debtors Falling Overdue\Summary Overdues Generated  This has always worked in the past but for some reason it no longer opens the CMD prompt.


Why are you running a separate command process for this when it could all be done with VBA within Excel?


----------



## dwilson38550m (Dec 27, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

Hi,

The VBA in Excel opens up CMD and run a sequence of operations to download invoices.


----------



## dwilson38550m (Dec 27, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

Hi I tried this but no luck. I will try a couple of other ways...maybe opening up a .bat is not the best way to do it...it can't even open up cmd     effectively I just need excel can to open cmd copy a list of invoices I need downloaded a3:a200 and save these to a specific directory.


----------



## Kenneth Hobson (Dec 27, 2018)

*Re: Command DoS Not Working (Excel VBA macro)- help please, hopefuly an easy fix?*

Give an example with obfuscated paths if needed.  Either an API or Workbooks(path).SaveAs method might be a better route.


----------

