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

[dwilson38550m]

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]

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]

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]

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]

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

 

[dwilson38550m]

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]

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]

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]

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]

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