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

dwilson38550m

Board Regular
Joined
Nov 21, 2005
Messages
89
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0
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:

Code:
    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
 
Upvote 0
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
---------------------------------------------------------------------------------


Rich (BB code):
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
 
Last edited by a moderator:
Upvote 0
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?
 
Upvote 0
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:
Code:
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,582
Messages
6,114,468
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top