locked
Working Batch Command Fails when passed to command line from VBA? RRS feed

  • Question

  • Hi Friends,

    I have a great working snippet that copies files from/to the destinations inside a .csv:

    for /f "tokens=1,2 delims=," %a in (xImageCopy.csv) do copy "%~a" "%~b"

    In a cmd window, if I Chdir to the directory where the (xImageCopy.csv) lives, and enter the above code, it works great.
    However, if I write it into a .bat file, it craps out and closes the cmd window ignoring my error handling, aaarrgh!

    I know I have the correct path and filenames. I even tried adding the entire path to the .bat file like this:

    for /f "tokens=1,2 delims=," %a in ("C:\Me\WasteMyLifeOn\StupidTests\xImageCopy.csv") do copy "%~a" "%~b"
    To no avail.

    Here is my batch code:

    Chdir C:\
    Chdir "C:\Me\WasteMyLifeOn\StupidTests\"
    if %ERRORLEVEL% == 0 goto :next

    for /f "tokens=1,2 delims=," %a in (xImageCopy.csv") do copy "%~a" "%~b" (FAILS MISERABLY)

    for /f "tokens=1,2 delims=," %a in (C:\Me\WasteMyLifeOn\StupidTests\xImageCopy.csv") do copy "%~a" "%~b" (ALSO FAILS)
    if %ERRORLEVEL% == 0 goto :batch

    :batch
    Echo Stupid thing failed yet again
    PAUSE

    :next
    Echo Path not found, check reference cell
    PAUSE

    I did see some help with UNC, but I'm not sure what is causing a working script to break like this?

    Any thoughts would be greatly appreciated!




    Friday, September 19, 2014 3:00 AM

Answers

  • Very simple:

    for /f "tokens=1,2 delims=," %%a in (xImageCopy.csv) do copy %%a %%b

    You need to double the % when in a batch.

    Remember that, if it is a CSV, the first line (header) will fail.


    ¯\_(ツ)_/¯


    • Edited by jrv Friday, September 19, 2014 4:03 AM
    • Marked as answer by Antialias401 Friday, September 19, 2014 4:13 AM
    Friday, September 19, 2014 4:03 AM

All replies

  • Very simple:

    for /f "tokens=1,2 delims=," %%a in (xImageCopy.csv) do copy %%a %%b

    You need to double the % when in a batch.

    Remember that, if it is a CSV, the first line (header) will fail.


    ¯\_(ツ)_/¯


    • Edited by jrv Friday, September 19, 2014 4:03 AM
    • Marked as answer by Antialias401 Friday, September 19, 2014 4:13 AM
    Friday, September 19, 2014 4:03 AM
  • Thank You JRV!

    I was seriously racking my head on that one.
    I appreciate you very much.

    Friday, September 19, 2014 4:13 AM
  • You are welcome.  It is documented but it is not obvious. 

    Happy batching and look closely into PowerShell as it is much less confusing.


    ¯\_(ツ)_/¯

    Friday, September 19, 2014 4:16 AM
  • Honestly, I had just finished trying PowerShell.

    It failed in the same way...

    $files=Import-Csv "H:\2 Schools\2014 - 2014\HS\Seniors\xImageCopy.csv"
    foreach($file in $files){
         Copy-Item $file.FileName $file.TargetPath
    }

    I call it from VBA and it opens and closes immediately, which was the same symptom I had with the CMD .bat
    I'm not really a coder, so I get stuck sometimes :-/ lol

    Friday, September 19, 2014 4:28 AM
  • How can you call PowerShell from VBA?  It is not possible to execute PowerShell scrip in VBA.


    ¯\_(ツ)_/¯

    Friday, September 19, 2014 4:34 AM
  • Right, VBA cannot run PowerShell, but it can be "called" with an existing PowerShell.ps1 file...

    Dim x
    x = Shell("POWERSHELL.EXE " & "C:\Another\CrazyPath\MyVery1stPShell.ps1", vbNormalFocus)

    And in the .ps1 file:

    files=Import-Csv "H:\2 Schools\2014 - 2014\HS\Seniors\xImageCopy.csv"
    foreach($file in $files){
         Copy-Item $file.FileName $file.TargetPath
    }

    But! It opens and does something that is too fast for me to notice and closes immediately, just like the .bat file was doing. But now, thanks to you, I have the .bat file working!! Yay!!


    Friday, September 19, 2014 5:00 AM