VB Help with .bat file

crocs1977

New Member
Joined
Nov 1, 2006
Messages
11
I can't seem to get a code to use for running a .bat file in a macro. I have seen several different codes, but they will not run the command. This bat file is ran, before running a macro in excel. I would like to include this .bat file update in the macro I am using in excel, basically eliminating a step.

The .bat file is located in a network drive, so I don't know if that matters of not. Here is the location of the .bat file:

I:\Call Center Reports\Daily ACD Report\export1\DailyACDReport.bat

I have tried using shell commands, but can't seem to find one that works.

This .bat file pulls reports from a server, as excel files. The macro in excel is just opening each of these updating information for reporting purposes.

Please help, thanks!
Call center geek
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Shell command like this should work:
Code:
Shell "C:\WINDOWS\system32\cmd.exe" & "I:\Call Center Reports\Daily ACD Report\export1\DailyACDReport.bat"
 
Upvote 0
This should also work:


Sub myBatch()
'Standard module code, like: Module1.

ChDrive "C"

'Open a Batch [.bat] File Option!
RSP = Shell(Environ$("COMSPEC") _
& " /c c:\ExcelTest\Bats\list.bat", vbNormalFocus)
End Sub

For: "I:\Call Center Reports\Daily ACD Report\export1\DailyACDReport.bat" it should look like:

Sub myBatch()
'Standard module code, like: Module1.

ChDrive "I"

'Open a Batch [.bat] File Option!
RSP = Shell(Environ$("COMSPEC") _
& " /I I:\Call Center Reports\Daily ACD Report\export1\DailyACDReport.bat", vbNormalFocus)
End Sub
 
Upvote 0
What OS are you using?

If you are on a Windows 98 Machine (and I think ME). You will need to use "command.exe" instead of "cmd.exe".
 
Upvote 0
Well, I tried the code:

Sub myBatch()
'Standard module code, like: Module1.

ChDrive "I"

'Open a Batch [.bat] File Option!
RSP = Shell(Environ$("COMSPEC") _
& " /I I:\Call Center Reports\Daily ACD Report\export1\DailyACDReport.bat", vbNormalFocus)
End Sub

This got me as far as the Command window, looking in the directory for the I:\Call Center Report\Daily ACD Report\export1>

That is as far as it got.

I am running Windows XP
 
Upvote 0
As far as I know XP does not allow a Command Window Run?

You could use VBA to Open the Workbooks you need. Batch file processing is not supported by new Operating Systems and is being discouraged.

This code will open every file in a folder, except itself:


Sub everyFileInFolder()
'Standard Module Code, like: Module1!
Dim wb As Workbook
Dim cell As Object, myRng As Object
Dim myValue As Variant
Dim myFileIndex&

Application.ScreenUpdating = False

With Application.FileSearch
.NewSearch
.LookIn = "C:\ExcelTest"
.SearchSubFolders = False
.Filename = "*.xls"
.Execute

For myFileIndex = 1 To .FoundFiles.Count - 1
Set wb = Workbooks.Open(Filename:=.FoundFiles(myFileIndex))

If wb.Name = "WorkbooksFromEachInFolder.xls" Then GoTo myNext

Set myRng = wb.Sheets("Sheet1").Range("A1:A5")

r = r + 1
c = 2

For Each cell In myRng
myValue = cell.Value
Workbooks("WorkbooksFromEachInFolder.xls").Sheets("Sheet1").Cells(r, 1).Value = wb.Name
Workbooks("WorkbooksFromEachInFolder.xls").Sheets("Sheet1").Cells(r, c).Value = myValue
c = c + 1
Next cell

wb.Close

myNext:
Next myFileIndex

End With
Workbooks("WorkbooksFromEachInFolder.xls").Save
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thanks, I am set on the macro for opening the workbooks, was just hoping to get rid of the manual task of running the .bat file, then updating the workbooks.

I appreciate you both trying, I didn't realize it wouldn't work in XP
 
Upvote 0
The Batch file run should work. Try this:

Code:
RSP = Shell(Environ$("COMSPEC") _
& " /c ""I:\Call Center Reports\Daily ACD Report\export1\DailyACDReport.bat""", vbNormalFocus)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top