get input from vba-Excel to batch script

Question

assume that if i have value at cell C9, sheet1 down here is the code that i use for run .bat script from VBA

> Blockquote

Fileset = Sheet1.Range("C9")

txtFpath = Sheet1.Range("C7").Value
FilePath = txtFpath & "\res.bat"

ChDrive "D"
RSP = Shell(Environ$("COMSPEC"), vbNormalFocus)
Application.Wait Now + TimeValue("00:00:03")
SendKeys "CD " & txtFpath & "{ENTER}", True
Application.Wait Now + TimeValue("00:00:03")
SendKeys "start " & FilePath & "{ENTER}", True
Application.Wait Now + TimeValue("00:00:03")
SendKeys "exit " & "{ENTER}", True
Application.Wait Now + TimeValue("00:00:03")
SendKeys "exit " & "{ENTER}", True
Application.Wait Now + TimeValue("00:00:03")
SendKeys "exit " & "{ENTER}", True


> Blockquote

and this is res.bat that i will use

Blockquote

@echo off
>summary.txt (
  for %%F in (*chkpackage.log) do findstr /l ###FileSet### "%%F" nul||echo %%F:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A
)

Blockquote

my question is how do i going to put that value from c9 into ### Fileset ### in the bat file

thank you for your time and answer

Francis Dean · Accepted Answer · 2013-08-01 08:37:37Z

1

You could use a parameter in the batch file to easily send the value of range "C9".

VBA code adding the parameter

SendKeys "start " & FilePath & " " & FileSet & "{ENTER}", True

Batch with with parameter

@echo off
>summary.txt (
  for %%F in (*chkpackage.log) do findstr /l %1 "%%F" nul||echo %%F:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A:N/A
)

EDIT: If the value of C9 has any blank spaces you'll need to add quotations around the parameter or it will only take the first word (Hopefully someone could suggest a much better way though!)

edited Aug 1, 2013 at 8:37

answered Dec 6, 2012 at 9:39

Francis Dean

2,5062 gold badges23 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

eathapeking Over a year ago

can u explain a little bit more i want to know that "%1" what does it stand for (i'm new to coding sorry)

Francis Dean Over a year ago

"%1" is the first command line paramater 'sent' to the batch file. Read more here about batch file paramaters: robvanderwoude.com/parameters.php

Collectives™ on Stack Overflow

get input from vba-Excel to batch script

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related