Free Microsoft Excel 2013 Quick Reference

VBA - Calling a .bat file or Python Script

Hi,

I'm banging my head against the wall here! I'm trying to use Excel to generate and instruction file for Python to run some complex calculations. Problem is, I can't get my Python script to run through VBA.

I've used variations of the 'Shell' command:
ie: Call Shell ("xxx", 1), Call Shell ("xxx"), Call Shell "xxx", Shell ("xxx", 1), Shell ("xxx"), Shell "xxx", etc.

As well, I have tried different string commands inside the Shell function:
Shell ("cmd") gives me the DOS Shell (in the C:WINDOWS directory)
Shell ("cmd /c python") gives me the Python Shell
Shell ("cmd /c python myfile.py") doesn't do anything

With regards to the .bat file, I have also tried calling that to invoke Python by methods such as this:
Shell ("e:myfile.bat")
Shell ("myfile.bat")

Anyway, nothing seems to be working for me. Please help! Thanks in advance...


Post your answer or comment

comments powered by Disqus
I'm working on my first VBA project that requires 12 fields to be manually input of which one field is a file path. I need this path for multiple reasons, but the one reason I'm having a problem with is using the location of the path in conjunction with calling a bat file or executing a cmd prompt that is at that path.

Path Location, minus file name:
sheets("b. Fill Out Required Info").Select
Range("B18").Select
Actual Path found in B18:
C:UsersNickDesktopSubmission ToolTest Files

File attempting to execute:
CopyFileNames.bat

I can manually put all of this together and get what I want by using:
The problem is, every time this will be used, a different path will be used, but the same batch file name will be used. So,
rather than having to manually input the file path every time, is there a way to replace "C:UsersNickDesktopSubmission
ToolTest Files" with the cell reference of the path listed above? I've tried different methods of this but I cannot seem
to get it.

It would be even better if I could just use the cmd prompt found within the batch and run it directly from vba; the prompt I'm running is as follows:
How can I put that cmd into VBA? I tried different versions of this with no success:
Notice that the path is the same as the path shown in B18 above. It would be great if that path would be dependent on the
cell referenced above.

Thanks in advance!

I've seen pieces of answers to pieces of this question but I've never seen a
complete answer that works. I can get close but there is always a problem
at the end. I thought I had done all of this kind of stuff myself over the
years and when a friend told me he couldn't get it working I just sent him
off examples of code that where the pieces I'd used. But in the end I'd
never used them exactly as stated below so my "do this and do that" memory
of what effects what just fell on its face.

Here's the complete flow needed.

Using the windows task scheduler:

A bat file is run

Excel is called and the "Auto_Open" macro is run.

Various VBA code does its thing, which does not include updating anything in
the workbook or does not include any kinds of prompt, dialog box. The VBA
code just reads and writes texts files and does nothing that would normally
require that an object or variable be set to NOTHING. The proper opens and
closes are done for the file numbers. Very vanilla code.

The workbook needs to close and that seems to happen.

Excel needs to quit, close, go away, gone-already, but it doesn't.

This is where I've seen dozens of "try this" suggestions but in the end
Excel doesn't quit, it just sits there with its grey panel with no workbook
open.

Ideally this whole process should run minimized as well.

Thanks for considering the challenge.

Hello all!

I dont know if this is the right forum for this, but I this is the best folrum I've found, so I'd thought I'd give it a shot anyway.

The scnario is that I'm running loads of SQL-queries in a bat-file (using the isql-command). After that has been done I want to run Excel to format the result of the queries, and save it as a excel file on a networkplace. The SQL-queries are working fine, and the macro is working fine, when run seperatly, but since I cant predict how long the SQL-queries will take I have to either give it an extra long time before I start with the Macro, or run then in a chain from the bat-file.
When I do that I usually end up with random errors in Excel. Most usually it is "Not enough memory to display completly". This error is shown before the actual workbook is loaded, and therefore no macro has been loaded either so there's nothing I can do from there.
When I click OK the macro runs as should be though, but since it should be run at 5 in the morning it will just stand there until I come into work each day, and have loads of complaint that they havent got their figures yet.

I was wondering if any of you know how to cuircumvent this/solve the problem?
I have checked out Microsoft, and googled the errors loads of times in diffrent way, but havent found anything solid that works yet.

Is it possible to Compress a Folder/File or create a Zip File Dynamically?

I have realized that VBA has a great potential and I have managed to create Files and Folders Dynamically using VBA coding..ofcourse with a lot of great help from the forum..

Going forward in saving time is it possible to Compress the Created Folders or any any Folders in a Given Path..

Like a WinZip file which can be an email attachment or on Pen-Drive..

Warm Regards
e4excel

is possible to lunch excel from a .bat file?
or open .xls file from a .bat?

EDIT: Subject by Jack

Is it possible to run a *bat file from a macro ?? if yes how??

For example the bat file name is test01.bat

How can I run this bat file from this sub

Sub mysub01()

Dim FieldOrderChk As String
FieldOrderChk = "=IF(ISERROR(VLOOKUP(R[-1]C,[Fields.xls]PDiff_Fields!R5C1:R50C2,2,FALSE)),"""",VLOOKUP(R[-1]C,[Fields.xls]PDiff_Fields!R5C1:R50C2,2,FALSE))"

If Range("A2").FormulaR1C1 = FieldOrderChk Then
ActiveWorkbook.Save
ActiveWindow.Close
Else
Application.Run "SuppInf.xls!DPDF_Field_Order"
Application.Run "SuppInf.xls!DPDF_Cntrz_End_Chk_Formula1"
End If
ActiveWorkbook.Save
ActiveWindow.Close

!!! Here run / exec test01.bat

End Sub

Thank you

From an external application to Excel, what would I need to do to change the value of a cell?

- Is there a web service api? (not interested in trying though Sharepoint)
- Some other API? Maybe COM?
- Is it possible through Excel VBA?

Also, I would like to receive events when a user changes a cell and inform an external application (such as calling a bat file or something).

I need both parts (events due to modifications and changing cells from external invocation) to work independent of the excel file opened.

Any thoughts, tips or reading material would be useful. I come from a non-microsoft development background so am not familiar with add in programming but I get the feeling the solution may lie there.

Thanks,
Raj

I have some Script (a .bat file) that, when run in the directory that it exists in, will delete all "old versions" of files (i.e.- the program creating the files {ProEngineer}, on each save, creates a new "version" of the file with a .1,.2,.3,.4,etc... appended to the end of it).
The script in the file is as follows: Code:
@echo off

Set MC = unset
if "%PROCESSOR%" == "INTEL_64" set MC=ia64_nt
if "%PROCESSOR%" == "INTEL_486" set MC=i486_nt
if "%PROCESSOR%" == "MIPS_R4000" set MC=r4000_nt
if "%PROCESSOR%" == "Alpha_AXP" set MC=alpha_nt
if "%PROCESSOR_ARCHITECTURE%" == "IA64" set MC=ia64_nt
if "%PROCESSOR_ARCHITECTURE%" == "x86" set MC=i486_nt
if "%PROCESSOR_ARCHITECTURE%" == "MIPS" set MC=r4000_nt
if "%PROCESSOR_ARCHITECTURE%" == "ALPHA" set MC=alpha_nt
if not "%winbootdir%" == "" set mc=i486_win95
if not "%MC%" == "unset" goto mc_done
echo ERROR Cannot detect what machine type you have.
echo Please make one of the following settings:
echo.
echo set PROCESSOR=Alpha_AXP     - For Windows NT Alpha based machines.
echo.
echo set PROCESSOR=MIPS_R4000    - For Windows NT MIPS based machines.
echo.
echo set PROCESSOR=INTEL_486     - For Windows NT Intel based machines.
echo.
echo set PROCESSOR=INTEL_64      - For Windows Intel Itanium based machines.
echo.
echo set winbootdir=   - For Windows 95/98 machines.
echo.
exit
: mc_done

if NOT "%mc%" == "i486_win95" goto postenv4096
  if "%env4096%" == "true" goto postenv4096
    Set env4096 = True
    command.com /E:4096 /C %0 %1 %2 %3 %4 %5 %6 %7 %8 %9
    set env4096=
    GoTo ptc_end
: postenv4096

set PRO_MACHINE_TYPE=%MC%

if "%PROOBJ_START_DIRECTORY%" == "" set PRO_DIRECTORY=
if NOT "%PRO_DIRECTORY%" == "" "%PRO_DIRECTORY%bin%MC%_ptc_setvars" %0 "purge" bat
if "%PRO_DIRECTORY%" == "" %MC%_ptc_setvars %0 "purge" bat
Call ptc_setvars.bat
del ptc_setvars.bat

if NOT "%mc%" == "i486_win95" set start_cmd=start ""
if "%mc%" == "i486_win95" set start_cmd=start

set PRO_DIRECTORY=%PRODIR%
if NOT "%PTCPATH%" == "" goto ptcpathset

if NOT "%mc%" == "i486_win95" goto not95
Set Path = "%PRO_DIRECTORY%bin;%path%"
GoTo pathdone
: not95
set path=%PRO_DIRECTORY%bin;%path%
: pathdone

Set PTCPATH = True
: ptcpathset


"%PRODIR%%MC%objpurge.exe" %1 %2 %3 %4 %5
: ptc_end
Currently, I am doing this by manually copying the purge.bat file to the directory that I want purged and double clicking it. Or by using an added right click action in Windows Explorer "C:WINDOWSsystem32cmd.exe /c cd %1 &"C:Program FilesproeWildfire 3.0binpurge.bat" *"

I would like to run this .bat file (as well as perform many other actions) from Excel VBA to purge directories based off of a value in Excel. I have tried to use VBA to copy the existing purge.bat file into the directory and run it through the shell command but this does not seem to work.

Note: I do not know much about the script except for what it does and that it was installed with the program (ProE).

I had been advised in a previous post http://www.mrexcel.com/board2/viewtopic.php?t=262396 to code the BAT file to accept a parameter and use %1 in the BAT file to do something with the passed string such as a subfolder but I am not sure how this would be done.

Would it be possible to re-write the script through VBA code and execute it?

Hey,

Do either one of you know how to execute a bat file from VBA code?

Like (pseudocode) do a call:
run(file.bat)

Thanks / Elin

Hello,

I am fairly new to VBA and I have a general question regarding coding efficiency. Is it more efficient to have a set of code in a private sub and then call it when needed or to repeat the set of code over and over again?

I have a userform with many controls on it. There are multiple checkboxes that I use to effect certain controls (e.g. fonts, visibility). For example, if checkbox1 is True, then the font on Label1 is bold, and visible is True (done to multiple controls). Then, for checkbox2, which is used for other purposes but also requires Label1 to be bold and visible.

I have multiple controls where this would take place. I know having a private sub to call would be easier for me when writing this code, but is it more efficient than just coding these changes over and over again?

Thanks.

Hi,
My question is on the subject line...
I have a worksheet which has 10-15 cells with long IF statements as
formulas.
The cell formulas are in the form of (not exactly of course):

=IF(A1=condition1 OR B1=condition2, display"first message", else
if(A1=condition2 AND B1=condition3), display"second message", else display
"third message")
etc.

I need to use my laptops internal speaker to sound an alarm (bip) or call a
*.wav file to play.... "if" any one of the cell value is "changed" because
of the conditions on any one of the cell is changed .

Can this be done via VBA?. I'd appreciate if code samples can be given.

TIA
J_J

hi!

I am looking for a VBA (or anything!!! yes..i am very desperate) which will enable me to run a .BAT file with parameters from Excel.

ie: if i have a .BAT file, which will ping a IP Address
.BAT will look like

@Echo off
ping %1

So, when i run from the command prompt, i could run as "filename.bat 192.168.0.10" -> which will ping the IP Address.

I want to achieve this thru an Excel File. Ie : there will be IP address in a Colum, and as users click on that, the system shoud run the .bat file, by taking the IP address given.

I even tried to call this as a hyper link, but was not able to pass the parameters.

Could any one PLEASE.... help me ?

-Ashly

Hello all,

I have a question but i am not even sure if this can be done. Here is what i currently have:

1. An outside program executes a bat file prompting the user for a name. Once you type the name, it saves a text file with a bunch of info on it to a specific folder.
2. The user then manually opens a specific excel file. During open, it asks the user to select the text file they just saved. It opens that file in another session of excel and sets it as the wbsource.
3. The main excel file then runs its VBA gathering information from that text file it opened, once finished it closes that text file, continues doing stuff, saves itself to a name thats in a specific cell's value, then closes excel.

Is there a way to avoid the manual opening and selecting of that text file by possibly using a batch file of some sort? That way, it will all be done automatically once you type the name and press enter in your batch file. I know how to tell it to open the specific excel file, but have no idea how to tell excel which text file to load after it opens.

Is this even possible?

Hello all,

Can I use VBA to play a WAV file or some other sound file format? I do not want the playing of this sound to open up an external
media player and be played inside of it, I want it to be a sound that just plays (like Windows and other programs events sounds).

--
Thanks for any help anyone can provide,

Conan Kelly

I am trying exec a BAT file with the contains:
==========
REM (call_XLS.BAT)
"example 1.xls"
exit
==========
But once it startes Excel, a command prompt window remains open until I
close Excel. Is there a way to have this close right away once Excel is
started?

I am actulay calling this from a PC REXX program and se no reason I could
not call the "example 1.xls" from a VBS file is that would be easier. I just
want Excel to open the Workbook without leaving the spawning window open.

Hello,

I'm trying to create a .bat file that will open excel, run a macro, then close excel.

If someone has an example or could point me in the right direction that would be great.

Thanks

Hello All

I have a spread sheet with a hyper link that opens a .BAT program. When I run it, it comes up with a box and says "Some files can contain viruses or otherwise be harmful to your computer.......................Would you like to open this files?"

If i press ok, the file runs.

But i do not want the message. Does anyone know who to stop the message from appearing.

Please Help.

Thanks in advance
Mushy Peas

I am trying to get VBA to open a text file called ni_wa_subinv_trans_XXXXXXX
and format in the Import wizard. The "ni_wa_subinv_trans_" will always be the
same , the "XXXXXX" will always be different. What can I do?
--
Regards,
Michael

Hi, I have the following question:

How can I run a DOS .bat (batch) file from within a Macro?

This is what I came up with so far and it seems to work. The only thing is that I need this to have relative paths, running from the same directory the Excel Workbook is in.

With absolute paths, this is what I have

The thing is I would need to run the web_hotel_check.bat file using a relative path...

Any ideas?
Thanks to all of your for your time.

Titus

Hi all

I need some guidance please. I have a button in my workbook which when pressed will call a pdf file stored on our Intranet.

The code I'd like to use is:

Private Sub CommandButton1_Click()
MsgBox "This link will open up a web page, if the webpage is not available please check that you have access to the Internet and contact your system admin", vbOKOnly + vbCritical
ActiveWorkbook.FollowHyperlink Address:="http://intranet/files/help.pdf", NewWindow:=True
End SubHowever, because its loading a PDF document after the user has clicked on my OK in the MSG box they are shown another warning dialogue box warning them about viruses. Is there anyway to disable that box, or to have it always answer OK and never CANCEL? You see, if they click CANCEL it goes into debug mode and I don't want that.

Also, is there any way of making the code safe if the file/page can't be found. Some kind of IF statement which returns a message box saying "I'm sorry that page can't be found" rather than a horrible VB error message!

Thanks so much for your help

Jon

We are mainly a UNIX environment so need processes that we can automate in a non-gui world, even if it has to be in a Microsoft environment. Basically we need to set up an automated process in which we can receive an excel spreadsheet, run a batch file which calls excel with a macro to extract certain data and create an output file that we can then use in our UNIX environment (preferably delimited ASCII).

The piece I need help with is how to run some macro (if that is what I need to do) from a batch file or command line, that will take as arguments an inputfile and an output file. The input file is an excel spread sheet the output file is ASCII data that I need to extract from the excel spreadsheet.

Once I have this I can get it back into the UNIX world no problem and be able to further process it as needed.

Your help is appreciated,
Tony

I am running a database that is generating pdf reports.
Now i am trying to use the NoData option, so if there is no data it should cancel the macro and quit the database, if there is data it should print and start a bat file to email the reports.

**************************************
This is the code i currently use in this report:

Option Compare Database
Option Explicit

Private Sub Report_Close()
Dim lngpid As Long

lngpid = Shell("C:bestandsnaam.bat", vbMaximizedFocus)
End Sub

Private Sub Report_NoData(Cancel As Integer)
DoCmd.CancelEvent
DoCmd.Quit
End Sub

*****************************************
But it is not working correctly.

The the printing of the report is been activated by using a macro (scheduled by a program called "HAL"

Regards,

Gerrit

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 ReportsDaily ACD Reportexport1DailyACDReport.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

I am calling a .bat file from a Java script using the Runtime.getRuntime().exec(execFile) command. The .bat file contains a line that starts Excel using a shortcut that also opens my workbook. However, when the workbook is open the workbook_open event does not run. When running the .bat file directly from a dos prompt the workbook opens and the workbook_open event runs fine. Does anyone know what would be preventing the workbook_open event from working when the workbook is opened using the Java script?


No luck finding an answer? You could always try Google.