Shell Function To Make This Run From A VB Macro

Jul 3, 2006

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 4 Replies


ADVERTISEMENT

Shell Function Compile Error - Expected Procedure Not Variable

Aug 8, 2014

I have a VBA shell script that worked just fine on another computer, but now it won't run.

The code that won't execute is:

[Code] ........

View 12 Replies View Related

Make Now Function Display Milliseconds In VBA Macro

Jan 29, 2014

I am using a VBA macro (Using mouse clicks as inputs for neuroscience experiment?).

What the macro does is it inputs a timestamp every time a keyboard combination is used (alt+right arrow in this case).

The part of the macro which inputs the timestamp is like this:

Sub AltRight_Sub()
On Error Resume Next
Cancel = True
Cells(Rows.Count, 2).End(xlUp).Offset(1) = Format(Now, "HH:MM:SS")
End Sub

I would like the timestamp to be displayed as HH:MM:SS.00, i.e. show the milliseconds. I tried playing around with the timestamp format (changing it to = Format(Now, "HH:MM:SS.00" or = Format(Now, "HH:MM:SS.0;@")) or = Format(Now, "MM:SS.000") and I tried using = Evaluate("=NOW()") instead of = Format(Now, "HH:MM:SS".

View 6 Replies View Related

Using Macro To Enter Variables To Shell Command

Oct 12, 2009

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 8 Replies View Related

Want To Make 1 Function From 5 Formulas

Aug 29, 2008

******** ******************** ************************************************************************>Microsoft Excel - Book3___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1ABC†2464104641046410†32081500002081520815†44062000040624062†51521259171521215212†Sheet1†[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

What I am doing is comparing Zip Codes from 3 Data Bases against each. If all 3 DB's are equal then I need a responce of "Ok", if there is a difference in any of the 3 then I need a "Bad". I know this is simple, but Data Base A can have the Zip Code as all 9 numerals or just the first 4. It only depends on who entered the Zip whether or not the trailing 4 numerals were entered or the were entered as all zeros. To throw another wrench in it, if the leading numeral is a zero, all three DB's will drop the leading zero.

I want to do all of this in 1 Function, not 5 fromulas.

What I need is for Database A, test the cell for Length and if it is 8 or 9 numerals then remove the right 4 to only leave the left 4 or 5 numerals (4 if it had the leading dropped 0) and then do a simple If A=B and A=C Then "Ok" Else "Bad".

View 9 Replies View Related

Shell Command In Vba

Jun 29, 2007

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 2 Replies View Related

How To Make A Conditional Median Function?

May 9, 2006

for my thesis i need to create median industry multipliers. i have a list of
companies with their industry codes and multiples, but now i need to create a
list with medians per industry. is there a function similar to SUMIF for
medians?

View 11 Replies View Related

Function In To Make Text Plural

Jun 23, 2006

I'm looking to copy 1000 cells of singular form text into the next column
and make it plural. Is there a function to do that?

View 9 Replies View Related

Make Udf Function Get Range From Otherworkbook

Jan 23, 2007

i need to create a udf that will take a cell parameter and then emulates( i guess thats the best word) the index/match functions. the reason im doing this - and not the index/match - is because excel has ran out of memory on me (external links stuff).

Set XLBookInventaire = Application.Workbooks.Open(strFilePathInventaire & "" & strFileNameInventaire, False, True)
line, the XLBookInventaire is still nothing

i believe its a limitation of the excel udfs, but i dunno how to avoid it or how to make this function work.

Function RechercherProduit1(cell As range)
Dim XLBookInventaire As Workbook
Dim MyCurrentSheet As Worksheet
Dim bNotOpenInventaire As Boolean
Dim XLSheetInventaire As Worksheet
Dim rngInventaire As range
Dim strFileNameInventaire As String
Dim strFilePathInventaire As String
Dim FaceArrInventaire() As Variant
Dim FaceRowInventaire As Long
Dim LastRowBColumn As Long, LastRowInventaire As Long
Dim LastWindowState As XlWindowState

LastWindowState = ActiveWindow.WindowState
Set MyCurrentSheet = Application.ActiveSheet
Application.ScreenUpdating = False.......................................

View 8 Replies View Related

Folder Structure By Shell

Nov 18, 2009

I want to find the folder structure, but walking down. I have try this code

View 2 Replies View Related

Way To Enter A Second Shell Command After The First One

Aug 11, 2008

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 9 Replies View Related

Shell To Open Word

Jun 2, 2009

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 9 Replies View Related

Make Index And Match Function Dynamic?

Aug 18, 2014

I have attached a workbook.

I have a calculated value in H2. This value will correspond to values in the table provided. This value is dynamic and will change based on criteria that will be added.

The value H2 is a combination of to "letters" D1 and A2 or A4 and B1 i.e. trace back where the intersection of H2 occurs within the table

In Cells A8 and A9 I am trying to represent which combination of letters will result in an intersection of the value H2. In the example provided it is C and A.

What i would like to do is have Cells A8 and A9 be more dynamic. So if for example the value in H2 changed to say R12,06 then the intersection would be B and B. Therefore Cell A8 = B and Cell A9 = B

The formula is cell A8 and A9 doesn't cater for this as the range is fixed. I need a dynamic range for the index and match function based on the where the intersection of H2 happens to be in the table.

View 4 Replies View Related

Make A Column Array Variable Within A Function

Aug 18, 2009

How do I make a column array variable within a function? I've done nested vlookups to get a column number, but the function I'm using doesn't use a "number", it uses a column aray. '=AVERAGEIFS(L:L,K:K,">="&B$2,K:K,"<="&B$3). The "L:L" needs to be moved according to Market, (or column choosen).

View 2 Replies View Related

How To Make The Result Of A TODAY Function Static?

Mar 22, 2006

I have a problem with the today Function. It appears that it changes each day. But that isn't what I want! I'd like to have a funtion that puts the current Date in a field when Data is being added in the Row and then having this date static the next day. Here is what I had so far but I have no clue to make the date static:

=IF(C10>0;TODAY();IF(D10>0;TODAY();" "))

in this case it checks for information in field C10 and D10 and if there is information it will add a Date like 2006-03-22. But new day the field will change to 2006-03-23 and that is not what i want. I want it to stay the same when data is put in and the date is being presented.

View 12 Replies View Related

Make Enter Key Function When Used Move Down One Cell?

Sep 23, 2013

Would any know a macro code to make the enter key function when used move down one cell and to the left ? Example. I am in cell B1 and I hit enter and the cursor moves to cell A2.

View 9 Replies View Related

VBA Shell Command On A .BAT (batch) File

Jul 13, 2009

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 15 Replies View Related

Shell Command Not Working With Windows 7

Nov 17, 2009

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 4 Replies View Related

Using Variable String In Shell Command

Nov 1, 2013

I'm trying to use a variable that hold a string in Shell string command. This shell is the command to take a snap picture over a given region and store the picture to a pre determined file name

***The code do compile***

The string variable is : StringAddress = "g: estsnapasnap1.jpg"

The following shell command will work

RetVal = Shell(("C:Program Files (x86)HyperSnap 7HprSnap7.exe " & " -hidden
-snap:x3602:y155:w1432:h830 -save:jpg g: estsnapasnap1.jpg"))

This one, with the string address variable, wont work

RetVal = Shell("C:Program Files (x86)HyperSnap 7HprSnap7.exe " & " -hidden
-snap:x3602:y155:w1432:h830 -save:jpg " & StringAddress)

View 1 Replies View Related

VB Shell Command To Browse Files

Oct 15, 2008

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 9 Replies View Related

Make SUMIF Formula Calculate With SUBTOTAL Function

Feb 21, 2014

I'm trying to exclude cells that are filtered in a separate sheet.

The sheet I'm working with gathers information from this separate sheet using the following SUMIF formula:

=SUMIFS(Claims!$H:$H,Claims!$G:$G,$A9,Claims!$B:$B,C$6,Claims!$E:$E,$B9)

This formula extrapolates information perfectly for me if I do not filter columns in the separate sheet with all of the information.

I know that SUBTOTAL functions can be used in a way to exclude hidden cells. Can I combine the SUBTOTAL function into my SUMIF function above to produce results that exclude filtered cells?

View 5 Replies View Related

INDIRECT FUNCTION - Make Data Stay Permenantly

Dec 9, 2009

I have approx 500 workbooks which contain custoemr details and I need to extract certain info (postcode, name, contact, phone number etc) and place it all on 1 spreadsheet. I was originally going to source data from unopen workbooks, but have realized I only need to do this task once, and I can maintain it from then on...


I have created a spreadsheet (attached) which uses INDIRECT formulas to retrieve the data. When I type in the file name in column A, Columns B to K fill themselves in. What I want to know is:

Once I open say, 10 Workbooks, TYPE the name in manually and all the other cells have filled themselves, I want to somehow KEEP the data there, rather than it return a #REF error when I close the workbooks to open the next batch.

Also, if you look at my workbook, to the far right is a range of data which I use in my INDIRECT formulas. Once I have filled all the cells with the relevant data, I want to be able to Filter my spreadsheet by Date Ascending. BUT I cant seem to do it because it includes my INDIRECT range in the filter and mess's up the formulas then I have tried Locking & protecting workbook, with Autofilter & select cells etc TICKED for users to use, but it still tells me to unlock workbook when I try to filter.

View 5 Replies View Related

How To Run Countif Function On Worksheet To Make Table Of Raw Data

Jan 11, 2013

I am trying to run a countif function on another worksheet to make a table of raw data.

Look on sheet SC-01 in Column H for "No" and return the count...pretty simple

Code:

=COUNTIF('SC-01'!$H:$H,"No")

What I need it to do is determin the sheet name based on the string in the column A

Control Countif Function
SC-01
4
SC-02
SC-04
SC-05
SC-06

I have tried various combos of Indirect and concatenate, but I keep getting a ref error.

View 2 Replies View Related

Excel 2010 :: Error In Shell During Opening PDF

Feb 25, 2014

I'm using excel 2010 and pdf creator to make a pdf (and open it automatically). It works with the following code:

[Code] .....

path and filename are previously defined objects.

I'm using pdfcreator because the code also has to be used with excel2003 on another pc. The problem is that I get an error (probably for trying to open the newly creating pdf-file).

Error 2417024894 (80070002) during execution. Method run of Object IWshSell 3 failed.

View 2 Replies View Related

Pausing Within For Loops Until Shell Command Is Complete

Nov 3, 2009

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.

Shell "C:Program FilesproeWildfire 3.0inproe.exe Mytrail.txt", vbNormalFocus

'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?

View 2 Replies View Related

Using Shell To Call Batch File Or CMD Prompt

Dec 9, 2011

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:

CopyFileNames.bat

I can manually put all of this together and get what I want by using:

Code:
Shell "C:UsersNickDesktopSubmission ToolTest FilesCopyFileNames.bat", 1

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.

View 9 Replies View Related

Possible To Reuse Opened Shell Command From Excel VBA

Jul 27, 2014

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

Set objShell = CreateObject("WScript.Shell")

[Code] .........

View 4 Replies View Related

Use The Shell Method To Open Files And Folder

May 11, 2007

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 7 Replies View Related

Reference Sheet Names With Indirect Function To Make Formula?

May 21, 2014

Attached I have a document where I am wanting all of the individual sheet names on the Total page. Rather than having to change each formula to match the sheet name I believe there is a way to reference the sheet name column (AO) in the formula so you can drag it down to fill in the columns. I am basic with Excel and do not know how to add this indirect function into my current formulas.

View 5 Replies View Related

VBA Shell Command To A Launch Applications On All Computers (no File Paths)

Apr 14, 2006

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 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved