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

 

[Kenneth Hobson]

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]

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]

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]

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]

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

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]

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]

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]

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.