Opening Command Prompt From Access..won't stay open! 1

[DBSSP]

Hi all. I have a question in regards to opening another application. I have code that opens the DOS command prompt. However I can't get it to stay open long enough to do anything. Any ideas on how to prevent it from closing? After I get that problem solved, I need to feed it (the dos app) a command. Below is my code currently.


***BEGIN CODE***
Function CmdPrmpt_()

Dim strCmdPrmpt As String
Dim strCmdLine As String


strCmdPrmpt = "Y:/cmd.exe"
Call Shell(strCmdPrmpt, vbNormalFocus)



End Function

***END CODE*** Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[rmikesmith]

Jay,

Try something like the following:

Sub TestDOSShell()
Dim strCmdPrmpt As String

  strCmdPrmpt = "cmd.exe /K" 
  Call Shell(strCmdPrmpt, vbNormalFocus)

End Sub

The above should keep the DOS window open until you issue the "Exit" command. To run your DOS program, try this modification:

Sub TestDOSShell()
Dim strCmdLine as String

  strCmdLine = "cmd.exe /C DosProg.exe Param1 Param2"
  Call Shell(strCmdLine, vbNormalFocus)

End Sub

The above will run your program then close the DOS session when the program exits. To keep the DOS window open after the program terminates, use /K instead of /C.

strCmdLine should include the full path and filename of the program to run, plus any commandline parameters needed by the program, using the appropriate syntax.


HTH
Mike

 

[DBSSP]

Thank you so much! One other question..I need to open Excel and a an Excel file before hand. Any ideas there? (Excel needs to be visible) Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[DBSSP]

I figured out how to get it open, but I can't figure out how to use a variable to select it for link updates. Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[rmikesmith]

Jay,

Post the code you are using to open Excel and workbook.


Regards,
Mike

 

[DBSSP]

Here's the code I am using. Everything is all fine and dandy so far, except for one minor thing. I need to update links on the Excel sheet before the command line can run correctly. I do have an "excel" varibale set for the application and have tried multiple things. I think that I'm just not setting the variable the right way or something...aahhhh, the strange and mysterious secrets of MS automation techniques.

***Begin Code***
Function Script_()

Dim XL As Excel.Application
Dim strExlLoc As String
Dim strCmdLine As String

'Set Parameters for command lines and Shells

strCmdLine = "cmd.exe /K Someapp.exe"

strExlLoc = "Excel.exe /K SomeWorkbook.xls"

'The switch "/K" can be replaced with "/C" to automatically
'close the command prompt when the program has executed.

Call Shell(strExlLoc, vbNormalFocus)

Call Shell(strCmdLine, vbNormalFocus)

***End Code*** Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[rmikesmith]

Jay,

Don't use the Shell command to launch Excel. Here is an example procedure I created in Access that opens Excel, makes it visible, opens a workbook while updating links, saves then closes Excel. You will need to set a reference to the Excel Object library in Tools | References (in the Access VB Editor) if haven't already.

Sub OpenExcelWB()
Dim XL As Excel.Application
Dim Wkb As Workbook

  Set XL = CreateObject("Excel.Application")

  With XL
    .Visible = True
    Set Wkb = .Workbooks.Open(Filename:="F:\Tek-Tips\Book.xls", UpdateLinks:=True)
    Wkb.Save
    .Quit
  End With

End Sub

You can further manipulate the open workbook through the Wkb variable, if needed.


Regards,
Mike

 

[DBSSP]

Thanks so much, this helps alot, and I learned something new to boot! Star for each of you! Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"

 

[DBSSP]

Whoops, I mean "a" star for "you", Jay
"If the words up and down were reversed, would you trip and fall or trip and fly?"