-1

I was trying to execute a batch file via standard call shell() function. This Batch file is project specific and created automatically for each project by an external application. Main function is to normalise around 40 files having statistical data used for my project. This data is being acquired form excel. While executing manually this takes around 30 seconds for the complete process and its working just fine.

When I try to access this using call shell function in VBA, It just pop up for like 2 seconds and outputs were not generated from Batch file. I am attaching My sample code below used for this. I am just baby-stepping in VBA Macros. Please Excuse my coding practice.

Call Shell(Range("L8") & "\DSTAT$.BAT")

I tried this also

`Dim Runcc
 Runcc = Shell(Range("L8") & "\DSTAT$.BAT", 1)`

Please let me know if any further information is required to sort this out.

Mofi
  • 46,139
  • 17
  • 80
  • 143
First_Steps
  • 19
  • 1
  • 5
  • 1
    You'll have to give us more information to work with... – Marcucciboy2 Aug 23 '18 at 19:19
  • What was the batch file meant to do other than run for a few seconds? Can you add a `pause` command to the end of the batch file? Providing examples of your code goes a long way in helping people understand what you are trying to do and how to help you. I'd recommend looking at the help center, particularly the [How to create a Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) section. – DavidP Aug 23 '18 at 20:03
  • Sorry David, Now I added more information to my query. Hope this helps. As I mentioned Batch file is being created specifically for each project that I am working on. Is there a better way than adding `pause` comment during each Project. – First_Steps Aug 24 '18 at 04:31

1 Answers1

1

Try

Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1 

With CreateObject("WScript.Shell")
    .Run Range("L8") & "\DSTAT$.BAT", windowStyle, waitOnReturn
End With

I use this for converting PDFs to JPG with Irfanview on report_open in Ms-Access.

Stolen from vba WScript.Shell run .exe file with parameter

ComputerVersteher
  • 2,638
  • 1
  • 10
  • 20
  • Thank you! But this doesn't help either. I am still having the same issue. – First_Steps Aug 24 '18 at 09:09
  • @Abhi Check path with `Debug.Print Range("L8") & "\DSTAT$.BAT"` copy string from immediate window and paste to commandline, maybe wrong sheet is used as you refer to `ActiveWorkbook.ActiveSheet` implicit, avoid that! – ComputerVersteher Aug 24 '18 at 09:51