Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










VBA Shell Command On A .BAT (batch) File


I was trying to run the shell command in VBA (excel2007) on a .BAT(batch) file. While running the macro, the DOS command prompt appears and goes away in a flash but the function of the .bat is not performed which makes me think that the shell command isnt functioning properly. I had this proper earlier also with a few Exe's.

the line of the macro that calls is (c is the path for the batch file) :

Call Shell(c, vbNormalFocus)

Could any of you help me with some suggestions regarding this problem.

Just to confirm, the batch file on itself works absolutely fine and it looks like it doesnt take more than 2 seconds to run it. It also does not wait for any input from the macro. it is an individual batch just included in the macro.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
VBA Shell Command To A Launch Applications On All Computers (no File Paths)
The VBA shell command is as follows:

programPath = "C:Program FilesInternet Exploreriexplore.exe" ' works
'programPath = "iexplore.exe" ' does not work
Shell programPath + " " + fileToLaunch, vbNormalFocus

but the drawback is that the invoked program (iexplore.exe, at least in my case) needs to have the FULL PATH to where the program exists = the "C:Program FilesInternet Explorer" which may or may not work on someone elses computer. This hardcoding will not work and is not transportable.

Is there a trick to find where the executing program lives? or launching it without the path?

View Replies!   View Related
Shell Command In Vba
I have to run command prompt from with my VBA code , and run an application passing it 2 filenames and redirecting the output of the application to a third file. Also my VBA script should wait for the application to complete running before getting back to the next line of execution. HEre is what i have coded so far :

Sub Macro1()
Dim dosCmd As String
dosCmd = "purge.exe input1.txt input2.txt >> opFile.txt"
Call Shell("cmd.exe " & dosCmd, vbNormalFocus)

Now this code ends up just opening the command prompt and nothing else. It does not run purge.exe.

View Replies!   View Related
Way To Enter A Second Shell Command After The First One
I have written a Shell command. It is very simple and designed to open another software. Here it is:

Dim X As StringX = Shell("C:Program FilesMicrosoft OfficeOFFICE11xlview.exe", vbNormal)

Is there a command that will close the open program? Something along the lines of
.....Ofice11xlview.CloseProgram", ...

But more importantly, is there a way to enter a second shell command after the first one that will have XLViewer to open a particular workbook after the viewer is open?

View Replies!   View Related
VB Shell Command To Browse Files
I'm in the process of building a user form that required the user to brows for a file, the path of the file that they select is then displayed in the textbox next to the "browse" button in my form. I also have another button next to this which when selected will open the file that is displayed in the textbox previously mentioned.

Iíve been trying the shell command but with no success, I can get the user form to open adobe reader but whenever I try to include that file path for which to open the file I keep getting runtime errors,

Below are a couple of examples that Iíve tried (you'll prob be able to guess from the code that Iím very new to VB in excel).

For additional info the value in the textbox would be something like, "H:My DocumentsPDF Filesmyfile.pdf"

Dim GetFile As String
Shell "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe", vbNormalFocus
'this opens adobe reader but I'm trying to open a specific file

Shell "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe" & textbox9.value, vbNormalFocus
This produces the runtime error again

Dim GetFile As String
GetFile = TextBox9.Value
Shell "C:Program FilesAdobeReader 8.0ReaderAcroRd32.exe" & GetFile, vbNormalFocus
'this produced a runtime error - to be honest this was an attempt born out of frustration.

View Replies!   View Related
Shell Command Not Working With Windows 7
I'm very much in a bit of a pickle. I have updated my operating system from vista to windows 7 and a macro no longer works. The old code is

View Replies!   View Related
Using Macro To Enter Variables To Shell Command
I want to run a Shell command to open lots of files.

I can get the basic command working, but I want to open a lot of different files and am seeing if there is a way to save time by having a variable input, rather than having 1000 different shell lines with IF statements.

View Replies!   View Related
Open .xls File From .bat DOS File & Save As .csv
What I would like to do is to open an excel workbook from a bat file in dos with one arguement.

so something like

start c:lacklist.xls 2315t

Once the blacklist.xls is opened I would like to have the macro to save the excel file as a csv file ignoring all the prompts and use the argurement 2315t as the directory to save it to on the j drive ie save to j:2315tlacklist.csv. Then close the workbook and continue with the rest of the commands in the bat file.

I'm guessing I have to use GetCommandLineA to read in the arguement but I can't find anything on the net that gives a clear explanation of how to use it.

View Replies!   View Related
Bat File Run
I'm trying to run a bat file from a vba macro. I've used x = shell("AllCopy.bat",1) and it runs but it doesn't do what its supposed to (i.e copy files into folders).

View Replies!   View Related
Run A .bat File From A Macro (using A Relative Path)
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.

View Replies!   View Related
Random Errors When Macro Is Run In A Bat-file
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 have checked out Microsoft, and googled the errors loads of times in diffrent way, but havent found anything solid that works yet.

View Replies!   View Related
Batch File
I know this is an excel forum but i'm struggling with something. i need to create a batch file that maps a network drive so far my searches have uncovered this

View Replies!   View Related
Running A Macro From A Batch File
Is their a way of creating a batch file which will open an Excel workbook and run a single macro?

View Replies!   View Related
Writing Batch File To Remove Spaces From Filenames
I realise this is not strictly an excel question but it forms part of my VBA code within excel

I have been writing a VBA program (with some fantastic help from you guys) part of which writes to a batch file to rename files stored in a given folder. Unfortunately this fails if the original files have spaces in the filenames. The batch file contains command lines such as: ...

View Replies!   View Related
Run Excel Macro From Batch File. Workbook Open
I wanted to run a batch file which does something like,

Dir /b >Test.csv
Start Test.csv

And then runs an Excel macro once the workbook is open.

I looked into this and discovered that itís not really that possible to run an excel macro through batch file but other options were to use ĎWorkbook_Opení etc.

I thought this might cause problems because Test.csv doesnít contain a macro to auto open and because csvís usually donít like macros.

I do however have personal.xlsb which opens every time if that helps.

View Replies!   View Related
Open .ppt File From Command Button
I have created a large process map workbook in excel that links to various areas within the document using command buttons.

However, one of the things I need users to be able to see s in a PowerPoint presentation.

All I need to do is create a command button that links to, and opens this .ppt file.

The .xsl and .ppt files will be distributed in a folder to other members of the team so I need the link to not be specific to my computer. Is it possible to avoid using the full path and just use a short path such as .../desktop/tools/process.xls so that as long as they store the folder on their own desktop the link works?

View Replies!   View Related
Turn Off Web Command Bar On Open File
I have a wb with hyperlinks that open other .xls files stored on the LAN. When these files open, the Web Commandbar is displayed. I ran a macro to turn it off, then pasted the following code in the wb module but it doesn't work.

Private Sub Workbook_Open()
Application.CommandBars("Web").Visible = False
Application.Goto Reference:=Worksheets("Deviation Form").Range("A1"), _
Scroll:=True
End Sub

View Replies!   View Related
Open URL Or Local Html File With Command Button?
What is the code to open a URL or local html file with a command button?

View Replies!   View Related
Shell To Open Word
not sure why this is not working, it is from Access I know this is the wrong forum but figured I haven't got any answers from anywhere else and it seems like a simple fix

this command fails... i am guessing its to do with the file path having spaces but don't know how to get it to work, what to insert so it will work.

Call Shell("V:Program Files (x86)Microsoft OfficeOFFICE11WINWORD.EXE / L:Best Practice ManualFull Labour Hire Best Practice Manual.doc", 1)

View Replies!   View Related
Folder Structure By Shell
I want to find the folder structure, but walking down. I have try this code

View Replies!   View Related
Batch Changes To Graphs
How can I make batch changes to graphs in the same excel workbook (but on different worksheets?)?

In particular, if I have a bunch of graphs, how can I make bulk changes to color, size, axes titles, etc.?

View Replies!   View Related
VBA What Is The Command To Scroll
Simple VBA What is the command to scroll to over "x" number of columns? and/or rows?

I just the active cell to move over 5 cells to the right for example?

View Replies!   View Related
Command Text VBA
I recorded this SQL query and got this and a Connection string, the query kind of works but i want to be able to edit more. Can someone tell me what does what in this huge string, i want to be able to edit the MatsViewerView.SpecType and other things like i can edit the lot number=2007312RC (very end of string). P.S. sorry about the formatting, i guess this is what excel does when it records a query onto a macro.

.CommandText = Array( _
"SELECT MatsViewerView.SpecType, MatsViewerView.LotNumber, MatsViewerView.WaferNumber, MatsViewerView.DieNumber, MatsViewerView.TestDate, PostAnalysis.Description, PostAnalysis.Achieved, WPGain1.WPGain" _

View Replies!   View Related
FIND Command In VBA
I need to find the row number of the first instance of the string 'TIMEACT
in column A. I know that it must exist at some point in column A, but I do not know where. Column A might have 100,000 entries... ie: a very large number, so I'd appreciate it if the code could be very general.

View Replies!   View Related
Batch Image Saving
Added pictures of the items to include on quotes (the pictures are in a column to the right of all the other details - (these pictures only exist in Excel)).
Our new quoting software tool needs a link to the filename - however, our pictures don't have logical filenames!

Is there a way we can do a batch save as of all these images so it ends up as 'part#'.jpg within folder X?

There are about 6,000 products and will be a bit of zombie work we don't really want to do!

View Replies!   View Related
Batch Tool Or Code
i have about 500 excel files that are built to specific format, only some data changes.

I need to make same changes to all the 500 files, so i'm asking that is there any kind of tool (free/commecial) that can batch changes to all files?

View Replies!   View Related
Macro Batch Save As
Is there a macro to batch save many xls files to text (prn) files?

View Replies!   View Related
Use The Shell Method To Open Files And Folder
I know this is simple, but im having problems finding it in search. Im using the Shell method to open files, but how to open folders?

View Replies!   View Related
Shell Function To Make This Run From A VB Macro
I'm trying to run an application using a VB macro. The command line provided to me is "C:Program FilesWeb Scraper Plus+PkgExec.exe" -p "5A147DC7-0420-4321-9DEC-02B8D8111A78". This command line works fine from my Run menu. Now how do I write a Shell function to make this run from a VB macro?

View Replies!   View Related
Vba - Is There Some Kind Of Loop Command For This
I have a spreadsheet that has a lay out like below

A A A
* A A
* A A
B B B
* B B
* B B

and so on where every letter is a new cell and * = empty cell

I need a peice of code that will highlight all of section A. Section A could have 1 row or an infinate number of rows.

What I need is like a peice of code that means that it will start selecting rows starting from "A3" which is the 1st cell with data and stop selecting rows when the next row has data in the A column.

View Replies!   View Related
Command Button VBA Code
So right now I have the
Private Sub CommandButton19_Click()
Dim product As Double
product = ActiveSheet.Range("J45").Value * ActiveSheet.Range("S44").Value
Sheets("Sheet3").Range("R2").Value = product
End Sub

I want to be able to switch the cell the product goes into based on a certain command button that I click(for example I have a command button for when I use copper wiring and one for aluminum wiring and I want when I click the copper wiring button for the product to show up in one cell, but when I click the aluminum button for the product to show up in a different cell , but on the same sheet). To be more clear I have a quantity command button(which is the code above) that i want to be able to recognize the difference between when the aluminum command button is clicked and when the copper command button is clicked.

View Replies!   View Related
Command For Checking A Checkbox In VBA
I have a worksheet with a series of checkboxes. I'd like to run a macro that would check SOME of them and then uncheck them.

I tried recording the macro first, but the recording did not recognize me checking the checkbox. I also tried writing a VBA script that would add a space (" ") to that checkbox and that seemed to work but not optimal for adding the check makrs back in.

View Replies!   View Related
Batch Import External Data
I have several thousand text files, each with three columns of numbers. My goal is to convert each of these to a dbf file with three distinct columns (which is why simply renaming each with .dbf at the end doesn't work.) I can do this for an individual file by using the "Import External Data" Tool in Excel, selecting the "Fixed Width" option (which divides the figures into three distinct columns), and then saving the file as a dbf. I need to do this for several thousand files, however, so I can't do each by hand.

View Replies!   View Related
SwitchBoard To Run Batch Files
I have made a userform that works as a switchboard, and when you click on a button it should run a code that runs a batch file.

but for some reason the code does not work, can someone show me where i am going wrong...

Private Sub CommandButton1_Click()
Shell "c:dataexcel oolkit downloadscombine files.bat"
End Sub

View Replies!   View Related
Deleting BuiltIn Command Bars VBA
I have the code below. But When I run it, the line marked * throws up an error.
I do have custom built toolbars in my spreadsheet. It should delete them all.
I am using Excel 2003.

Sub DelCommandBarsCustom()

'Del all command bars of specific criteria

For Each objCommandBar In Application.CommandBars
If objCommandBar.BuiltIn Then
* objCommandBar.Delete
End If
Next objCommandBar


End Sub

View Replies!   View Related
VBA To Pass Command Line Parameter
I found the following link that got me started [url], so now I have my code for reading/analyzing comand line parameters. Here is an example of the parm I'm passing in ....

View Replies!   View Related
Sum Up The Batch Quantities For A Date With Variance One
I need to sum up the batch quantities for a date with variance one...
but it doesn't work... I suspect that I'm using wrong formula, it should be not SUMPRODUCT...

when I tried to use just SUM, it adds all the quantities in the colomn.

=SUMPRODUCT(--(($AB$11:$AB$100)=AK12),--($AG$11:$AG$100=1),($AD$11:$AD$100))



View Replies!   View Related
Batch Process 1 Macro In Multiple Files
I have a collection of about 200 .xls files in a folder, each contain the same macro. I now need to run this macro in all 200 files and save them but to do this manually will take hours. is there anyway to set excel to batch process a macro in multiple files?? Or maybe i can write a new macro to open up each workbook, run, then save and exit??

View Replies!   View Related
VBA Sort Command Button & On Save
I have a sheet name "all" Sheet 15. What I want to do via VBA is sort range B4:I803 sort by Column G (ascending) then by Column H (ascending).

Have this sort activated by a command button or once the WB is saved then activate the sort prior to saving. The former is the preferred but if it's not to much trouble with coding then the former and latter solution would be great.

View Replies!   View Related
VBA _Change() Command; New Values Returns An Incorrect Value
When I use that function on a UserForm it executes the code immediately, not waiting for the value to be entered. Thus the code executed that relies on the new values returns an incorrect value.

Example

A1 | 150
A2 | 100 <-- this is the value being changed to 300 with the Textbox
A3 | 250 = Sum(A1:A2)

Now with:

View Replies!   View Related
Using Command Buttons To Change Vba Code On Worksheets
I am trying to create a program to automate the gauging figures on oil barges, currently everything is done by hand and takes approximately 20 minutes to complete. I have the charts entered for the tanks already and have the code set so that when you click on a tank "gauge" it will enter the "volume" which corresponds to that gauge on a totals sheet. Here is where my problem is coming in

The barges are gauged at four points
"before loading"
"after loading"
"before discharge"
"after discharge"

I want to set a worksheet as the default page with four command buttons that let the user select which operation he wants to perform. before load figures. after load figures and depending on which operation they select have it enter the volumes in the appropriate cells on the totals sheet. So if someone selects "loadopen" command button I want the following code to run on my worksheets............................


View Replies!   View Related
Copying Data As Values From One Sheet To Another- Using A Command Button VBA
I have a Sheet in which the data is calculated by changing the Sheet reference and also by the period..

Now I need to transfer this data as values, however there are certail cells which are merged and therefore I get a prompt as I cannot paste as values using Paste Special-> Values..

View Replies!   View Related
Convert Text Delimited File To .xls File Using Vba
how to word it but if someone understands then please help. I have two excel data files namely Book1.xls & Book2.xls. Both files have different data in it. Both files contain macros. When these macros run the files become **FINALIZED** version.

Originally, I get the above files in my email as txt. attachments. I then move these two txt files to my desktop in a folder called Folder-1. Then I open these files as an Excel and save them.

Basically, I need to know if two txt files are sitting in a folder-1 on my desktop. What can I do or what can I clik that....those two text files get converted into excel automatically, including running that macro I talked about in the above paragrah.

To put it differently, if I have two txt files Book1.txt, Book2.txt in a folder, how can I automatically create an excel **FINALIZED**version which sits right next to their txt version.

View Replies!   View Related
Press Command But And Execute The Code Assigned To Another Command Button
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).

View Replies!   View Related
Command For Target: Send A Target Link To A File
i am needing to issue a dos command in excel? basically i need to send a target link to a file. i cant use a hyper link for several reasons, and this is the only way i know how to go about this.

View Replies!   View Related
VBA: Seperate Vba Code From File
Is there a way to put vba code to a standard alone exe file? This way, I don't have to keep update the code on different excel files.


View Replies!   View Related
Disable The Vba In A File Being Opened Using Vba
I have excel vba code to open .xls files on my server. Files to open are chosen by the user via a UserForm within my app, it shows them all their files in their directory on my server. How can I open their files (.xls) that they select but DISABLE the VBA from being able to run/execute, if they have any attched to the file?

I now have all code in place to open the file no problem, I just need to know how to disable the VBA part from being "turned on" when the file opens.

View Replies!   View Related
Column Letters- VBA Command To Get The Letters
Is there a VBA command to get the letters, instead of the numbers, of the column of a selected cell?

I have to letter a list whihc means setting up a loop using character codes.

I may have to go into double letters so I am working on how I would set up the loop for if and when it gets past 90 and starts on double letters. so far the highest is the letter "U"

of course the easiest would be to pick up a column value as a letter

NT values do not get a number

A_____ _____NT###
B_____1_______C####
C_____2_______RMK###
D____ _______NT####

i am guessing the loop might involve some arithmetic test between the count and the character set 65-90. or maybe a mod thing.

View Replies!   View Related
What Command Is Executing Or What Was Last Command Used
Is there any way to tell (from VBA) what GUI command is currently in progress or what the last command used was?

On a wish list perhaps: Application.LastCommand and/or Application.CurrentCommand

Let's say I'm in the "Sheet_Change" event and want to know what caused the change. Was the event triggered by "Delete", "Paste", "Keystrokes" ...

AutoCAD VBA has "Begin_Command (CommandName as String)" and "End_Command (CommandName as String)" events which I find very useful. Basically I'd like to emulate that to the greatest extent possible.

Application.Caller does not seem to work for the stated purpose. I've been searching for a workaround by means of reading the "Undo Stack". Numerous articles written by experts state that the undo stack is not accessible from VBA.

I've considered reading the text from the "Undo" button's caption but it just seems like such a hack ... not that this whole idea isn't a hack

View Replies!   View Related
VBA Vlookup From Another File
I am working on a new project and am trying to use Vlookup in VBA.

The code i have so far is:


For Each cell In Range("B2:B" & CurrRow)
cell.Value = Application.WorksheetFunction.VLookup(cell.Offset(0, -1).Value, Workbooks(Comparefile).Worksheets(CompareActive).Range("A:A"), 1, False)
Next cell
CurrRow is the last row in the active sheet.
I have a workbook defined as Comparefile (not the entire filepath) and a worksheet as CompareActive.

Both files are opened...

The macro works untill this line, i don't get an error message but i just don't get any results...
Is there anything i do wrong? I really can't get it to work.

View Replies!   View Related
File Protection With Vba
I need to protect excel file. I have search vba codes and tried to combine :
1. Must enable macro on open
2. Input user id and password
3. Disable cut, copy, paste and print
but not work.
user : test
password : 1
Details as per attachment


View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved