run a windodws command from macro

[broecher]

When scrolling through the possible actions in the macro builder, I see a few items that say run or command or something like that. Is there an action thatt will run a command in the windows command prompt?

I want to export a lot of csv files that then have to be processed by a python program (not written by me obviously). I currently open a cmd window and navigate to the folder and type myprogram.py f1.csv and it does its magic.



Since I am using a macro to export a lot of these, it would be nice if I could just add another action that makes the python run also.

Is this type of thing possible? Maybe I need to convert to vba and there is a simple way to do this there???

Thanks in advance!

[ajetrumpet]

as far as I know, you need VBA for this. you can use it in a macro with the RUNCODE command. This requires either piping or text file streaming to manipulate DOS through VBA. I wrote a FAQ on how to do this.

This page shows how you can use FTP file transfer through VBA: http://www.ajenterprisesonline.com/f...read.php?tid=7

I'm not sure if you can actually substitute "cmd" in for "ftp" or any derivation of it. I've never tried. Might be worth a shot though.

[broecher]

thanks for steering me in the right direction. It looks like what I want to do could be done without the text file possibly??? If I just had a module with a line that looks something like this:

shell "cmd.exe /C myprogram.py c:/path/f1.csv", vbNormalNoFocus

I can't test anything until I go to work tomorrow...

[ajetrumpet]

thanks for steering me in the right direction. It looks like what I want to do could be done without the text file possibly??? If I just had a module with a line that looks something like this:

shell "cmd.exe /C myprogram.py c:/path/f1.csv", vbNormalNoFocus

I can't test anything until I go to work tomorrow...

I don't remember finding anything that ever worked with more than one command line in DOS. There is a bunch of code out there to open DOS and run one line, but that's very limited. As an alternative though (if you have more than one line to run), you could stream a txt and rename it as a batch file, then shell it...?

Dim fs, oFile

    Set fs = CreateObject("Scripting.FileSystemObject")
       
   Set oFile = fs.createtextfile("c:\txtCommandLines.txt", True)

   oFile.writeline "dir" 'what do we have in this dir?
   oFile.writeline "chdir.." 'lets go back
   oFile.writeline "dir" 'whats in this one?
   oFile.Close

   Name "c:\txtCommandLines.txt" As "c:\txtCommandLines.bat"
   
   shell("c:\txtCommandLines.bat", vbHide)

    Set fs = Nothing
    Set oFile = Nothing

that's the only way I can see that you can execute multiple cmd's in the prompt. there just isn't any examples out there.

[broecher]

oh, there is a 1 line limitation.... I understand now. that looks really useful! Can't wait to give it a try.

Thanks again

[ajetrumpet]

if it's one line, this article is great: http://www.vbaexpress.com/kb/getarticle.php?kb_id=971

good luck!