Open & execute Command Prompt from a Form

[michellecole]

Is there a way to programatically call a command prompt and execute commands from an on_Click event from an MS Access Form?

What I am wanting to do, (unless there is another way to do it), is allow the user to click a button which will concantenate several "*.file" files into a single ".file" before the form imports it. I can concantenate by manually opening a command prompt, then changing the root directory, then typing copy RF*.file SingleFile.file. I'm wondering if I can automate somehow?

Thank you,
Michelle

 

[PHV]

You may try something like this :
CreateObject("WScript.Shell").Run("%COMSPEC% /C cd ""\Path\to\root dir"" & copy RF*.file SingleFile.file", 2, True)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[michellecole]

Thank you for your quick response PHV, I have been unsuccessful in getting this to work so I thought I would send you my code - I'm sure I am leaving something out. It looks like the command window flashes on the screen, but too quickly for me to see what it says and the new file does not get created.

Many thanks,
Michelle

:
:
Private Sub cmdContantenate_Click()
On Error GoTo Err_cmdContantenate_Click

If MsgBox("ALL RFILES in directory: kcpool2\pool\operations\cashmgmt\ACH\BalancingUtility\ will be concatenated into a single new file.", vbYesNo, "Concatenate RFILES?") = vbNo Then
Exit Sub
End If

Dim sSourceFiles 'Multiple RFILES
Dim sDestinationFile 'Single RFILE
Dim sDate As String
Dim sTime As String
sDate = Format(Date, "yyyymmdd")
sTime = Format(Time, "hhmmss")

sSourceFiles = "\\kcpool2\pool\operations\cashmgmt\ACH\BalancingUtility\RFILE*"
sDestinationFile = "\\kcpool2\pool\operations\cashmgmt\ACH\BalancingUtility\RFILE" + sDate + sTime + ".file"

CreateObject("WScript.Shell").Run ("%COMSPEC% /C copy " & sSourceFiles & sDestinationFile)

Exit_cmdContantenate_Click:
Exit Sub

Err_cmdContantenate_Click:
MsgBox Err.Description
Resume Exit_cmdContantenate_Click

End Sub
:
:

 

[PHV]

And this ?
CreateObject("WScript.Shell").Run ("%COMSPEC% /C copy " & sSourceFiles & " " & sDestinationFile, 2,True)

To see the error message:
CreateObject("WScript.Shell").Run ("%COMSPEC% /K copy " & sSourceFiles & " " & sDestinationFile, 1,True)

Anyway you have a problem as RFILE" + sDate + sTime + ".file" is grabbed by RFILE*"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[michellecole]

CreateObject("WScript.Shell").Run ("%COMSPEC% /C copy " & sSourceFiles & " " & sDestinationFile, 2,True)

The above syntax yields the following Compile Error "Expected: =". I removed the last part of the syntax, ",2, True)", to get past it and ran it SUCCESSFULLY!! Is this syntax required and what does it mean?

Thank you!!

Michelle

 

[PHV]

The 2 is for hidding the window
The True is to pause the VBA until the end of the copy
CreateObject("WScript.Shell").Run "%COMSPEC% /C copy " & sSourceFiles & " " & sDestinationFile, 2, True


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[michellecole]

I need he VBA to pause, but I am getting a compile error. What am I doing wrong?

This code works - but I need a pause:
CreateObject("WScript.Shell").Run ("%COMSPEC% /c copy " & sSourceFiles & " " & sDestinationFile)

This code yields compile error as noted above:
CreateObject("WScript.Shell").Run ("%COMSPEC% /C copy " & sSourceFiles & " " & sDestinationFile, 2,True)

I've tried several variations:
Compile Error: CreateObject("WScript.Shell").Run ("%COMSPEC% /c copy " & sSourceFiles & " " & sDestinationFile &, 2 ,True)
The Syntax of the Command Line is Incorrect: CreateObject("WScript.Shell").Run ("%COMSPEC% /k copy " & sSourceFiles & " " & sDestinationFile & ", 2 ,True")

Any other ideas?

Thank you,
Michelle

 

[PHV]

Have you read my previous post (2 Aug 05 11:46) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[michellecole]

Gotcha! Thank you. Thought you were just referring to the 2 and True questions - didn't realize it was new code.

All is good.

Thank you,
Michelle