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.
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.
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.
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.
I have written a For loop that uses a shell command to call a batch file that replaces text in some text file, Mytrail.txt. I then use that altered text file and use it to run a 3D modeling program Pro/Engineer by calling it through another shell command. I would like to add pause instructions to my routine after the shell commands. Below is a snippet of the code I am working with:
For i = 1 To N Step 1 Shell ("Reblacer.bat Mytrail.txt string_A string_B)
'Here I would like to add code that will pause the routine until the batch file 'operation is complete.
'Here I would like to add another pause instruction until the above shell 'command is complete. Note that this shell command creates a new file in 'some directory whose name is actually string_A.prt. Could this be used to 'tell visual basic that it may recontinue executing the routine.
Next i End Sub
How to add pauses to the routine above that prompt visual basic to continue executing the code only after the shell commands have completed their tasks?
I am using a shell command to communicate with a device under test and would like to open this shell once, change directory to a specific one and then send different text strings to it at various times during my test program. The code below works perfectly fine but opens and closed the shell each time it is used. I think it woud be quicker if I could reuse it until done and then exit out of it.
Here are 2 examples of a couple different strings I would like to pass into the cmdShell function at various times. I would like to send the first string, perform some measurements and then repeat with the 2nd string.
i.e. shellStr1 = ("cd .. & cd c:ezspi & testdm900 --widn 0x1020 --widv 0x1") then once the shell is open and the directory has been set send shellStr = ("testdm900 --widn 0x1020 --widv 0x5")
Is this possible using WShell Script or do I need another method?
Public Sub cmdShell(shellStr As String) Dim Test As Double Dim response As VbMsgBoxResult Dim cmdObject As Object Dim ReferenceTime As Double, ElapsedTime As Double
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?
I have the below VBA code that I'm working on where I run it after I already have a workbook entitled BRNewBusiness121106.xls (or whatever day it is when I'm running the code) that's open, and the code opens another workbook, BR-MasterAccounts.xls and then does some work between the two workbooks. The code is stopping with a "subscript out of range" error at this line: Windows("BRNewBusiness" & Format(Date, "mm/dd/yy") & ".xls").Activate
Why is it doing this? What change do I need to make so the code will activate that already-open workbook?
Sub testAfterDAKCSupload() ' Application.DisplayAlerts = False
MsgBox "new code follows...deskcheck before putting into production" 'edit the below remaining steps to get working properly
I have a macro to collate data from multiple workbooks to a single sheet. The folder path to the source files can be chosen thorugh the macro. It runs perfectly in my system running on Windows XP. But it doesnt run on Wondows 7 system. Both are using MS Excel 2010. In Windows 7 system, while choosing the folder path where source files are located, it says "No items match your search" (screenshot attached). I would want the macro modified so that it runs on any platform. The macros that I'm using is given below:
I have made a list box and can see the data in it and make multiple choices. What I need to know how to do is put a small button and make the list box appear and make the selected choices, then hide the list box but the button visiable. I would to have check boxes and a scroll bar in the list box by the names but i am stumped. If you could show the code that will make this work that will be great!!! lets say cell B3 to B6 shows cat, dog, mouse, bird. I would check the box and choose cat and bird and have only these 2 show up in the cell B2 separated by a comma. So each line for about 100 lines down will need to have the same choices made but with equipment names, people ect. or is there something better that will work. I run Excel 2003 and windows xp.
I've recently created a macro which takes a .csv file and scrubs the data and dresses it up for a report. I've finally got it running without any problems and have added it individually to 5 different user accounts on my network as an add-in, but need a way to share it across a computer network without allowing others to edit the file.
The report is generated multiple times a day by the different users, none of whom are very computer literate (not that I am much better, myself), and the macro over-writes the old file each time. I've read that it's possible to simply create a shortcut to the file that automatically opens the spreadsheet in read-only format, which would be perfect for what I'm trying to do, but I haven't been able to get it to work so far.
On my computer at work (Windows 7) the shortcut simply opens the spreadsheet so that anyone can edit it, instead of as a read-only file. On my computer at home (I'm trying to experiment) (windows 8.1) it won't even let me save the shortcut, saying I need to provide administrator permission & then cutting to a window that says "access denied".
I won't have the pathway for the actual file I'm trying to create a shortcut for until tomorrow when I get back to work, but I've formatted it essentially the same, directing to the excel.exe file, running the switch "/R", and then directing it to the actual file location.
The ultimate goal is to have multiple people be able to view the file without locking out those who need to update it.
I am using Excel 2010. I am using a shared workbook that was originally created in Excel 2003. This workbook has a command button that is supposed to be clicked to open a userform. However, I cannot even click on the button when opening the shared form in Excel 2010. When the form is not shared, the button works fine.
I have no problems with the button when opening the shared form in 2003 or 2007.
Is there anything I can do to make the command button work while the form is shared and while using Excel 2010?
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?
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:
Code: sheets("b. Fill Out Required Info").Select Range("B18").Select Actual Path found in B18: C:UsersNickDesktopSubmission ToolTest Files
File attempting to execute:
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:
Code: cd C:UsersNickDesktopSubmission ToolTest Files dir /b/o |find ".xml">ListOfFileNames.txt
How can I put that cmd into VBA? I tried different versions of this with no success:
Code: Shell "cmd.exe /c cd C:UsersNickDesktopSubmission ToolTest Files dir /b/o |find ".xml">ListOfFileNames.txt"
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.