calling .bat file from Excel

Trying to get .bat file successfully called from Excel

Page 1 of 1

4 Replies - 28320 Views - Last Post: 06 September 2007 - 07:49 PM Rate Topic: -----

#1 sjc   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 16-August 07

calling .bat file from Excel

Posted 16 August 2007 - 02:55 PM

Hello. This is my first post on DreamInCode. I've been programming for a while, but don't have any formal training in it, just what I've gotten from reading some books.

I'm trying to call a batch file from Excel. The batch file produces some other text files that I eventually want to read back into Excel. While the call to the batch file seems to work (a command window opens and a bunch of text flies by), the files that should be generated never appear. Here's my VBA code in Excel for calling the batch file:

Private Sub Run_Prog_Bttn_Click()
	Dim Test As Double
	Dim Response As VbMsgBoxResult
	Test = Shell("D:\TestRunforCall\TestRun.bat", vbNormalFocus)
	If Test = 0 Then
		Response = MsgBox("The Specified Batch File Does Not Exist.", vbOKOnly, "File Error")
		Exit Sub
	ElseIf Test > 0 Then
		Response = MsgBox("Successful call to Batch File.", vbOKOnly, "File Run Report")
	End If
End Sub



Everytime I click the button to call this Sub, the "Successful call to Batch File" message appears.

If I go to the folder where the batch file is located, I can double-click it and it runs fine, putting the files it generates into the folder with the batch file. So I know the contents of the batch file are correct. Something just isn't right with the Excel call.

Can anyone help me to understand why it's not working? I've looked all over and the files aren't being written anywhere else. They're just not being written at all.

Also, if it would be better to post this in one of the other forums, please let me know. After looking at their names, this one seemed to be the best place.

Thanks for any help you can share!

Sean

This post has been edited by sjc: 16 August 2007 - 05:10 PM


Is This A Good Question/Topic? 0
  • +

Replies To: calling .bat file from Excel

#2 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: calling .bat file from Excel

Posted 16 August 2007 - 05:15 PM

Try this,

Private Sub Run_Prog_Bttn_Click()
	Dim Test As Double
	Dim Response As VbMsgBoxResult
	Dim sYourCommand as String
	sYourCommand = "D:\TestRunforCall\TestRun.bat"
	Test = Shell("cmd /c " & sYourCommand, vbNormalFocus)
	If Test = 0 Then
		Response = MsgBox("The Specified Batch File Does Not Exist.", vbOKOnly, "File Error")
		Exit Sub
	ElseIf Test > 0 Then
		Response = MsgBox("Successful call to Batch File.", vbOKOnly, "File Run Report")
	End If
End Sub



Hope this helps :)

Happy Coding!
Was This Post Helpful? 1

#3 sjc   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 16-August 07

Re: calling .bat file from Excel

Posted 06 September 2007 - 12:38 PM

Thanks very much. That seems to help. I had to adjust the paths a bit too for the .bat file and the files it calls, but everything does work now. Thanks again.
Was This Post Helpful? 0
  • +
  • -

#4 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5223
  • View blog
  • Posts: 14,002
  • Joined: 18-April 07

Re: calling .bat file from Excel

Posted 06 September 2007 - 02:31 PM

Psycho is good at what he does there is no doubt about it! He is the man! :)
Was This Post Helpful? 0
  • +
  • -

#5 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: calling .bat file from Excel

Posted 06 September 2007 - 07:49 PM

View PostMartyr2, on 6 Sep, 2007 - 02:31 PM, said:

Psycho is good at what he does there is no doubt about it! He is the man! :)


Why thanks Martyr2 :blush:

Programming is my life :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1