8 Replies - 102204 Views - Last Post: 14 July 2006 - 03:39 AM Rate Topic: -----

#1 Mystrunner  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 07-July 06

Running Command Prompt in VB/VBA

Posted 07 July 2006 - 07:34 AM

I'm hoping this is the right place to post this;

I'm working on creating an access application, and am attempting to use the command prompt to connect to an FTP. I already know all the code needed to connect to the FTP via the command prompt, my problem is, I have no idea how to summon the command prompt or pass commands to it! I was thinking that I could somehow use the createobject(*.application) function, but I've not had any luck so far.
Does anyone have any idea on how to call a command prompt?

Thanks in advance!

Respectfully yours,
Myst

Is This A Good Question/Topic? 0
  • +

Replies To: Running Command Prompt in VB/VBA

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Running Command Prompt in VB/VBA

Posted 07 July 2006 - 07:43 AM

In VB6, a simple Shell command should suffice, such as
Call Shell "cmd.exe"


For VB.NET, the system.Diagnotics namespace should be fine:
System.Diagnostics.Process.Start("cmd.exe")


I'm no VB guru, however...so some of our other members may have a better suggestion.
Was This Post Helpful? 0
  • +
  • -

#3 Mystrunner  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 07-July 06

Re: Running Command Prompt in VB/VBA

Posted 07 July 2006 - 07:51 AM

I'm not sure if the fact that it's VBA contained in Microsoft Access, but those methods don't seem to get any results. I'm gonna fiddle around with them more, though. It might just be something on my end.
Was This Post Helpful? 0
  • +
  • -

#4 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: Running Command Prompt in VB/VBA

Posted 07 July 2006 - 08:08 AM

Hmm...so something like the code listed on the following page does not work?

http://www.techonthe...s/other_app.php

It specifically discusses launching applications from Access, both through the Shell command, and through the createObject process...
Was This Post Helpful? 0
  • +
  • -

#5 Mystrunner  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 07-July 06

Re: Running Command Prompt in VB/VBA

Posted 07 July 2006 - 08:12 AM

I just tried running it, and it brings up the error:

"Compile error:
Expected Sub, Function, or Property"
Was This Post Helpful? 0
  • +
  • -

#6 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Running Command Prompt in VB/VBA

Posted 07 July 2006 - 08:45 AM

Can you post the code you have so far?

Usually this message means you have code outside of a procedure or function.
Should be like this:
Private Sub CallApp()

	 Call Shell("C:\WINDOWS\system32\cmd.exe", 1)

End Sub


You could either make a call to this procedure to execute or put the code
Call Shell("C:\WINDOWS\system32\cmd.exe", 1)
inside the procedure of a button click event.

This post has been edited by jayman9: 07 July 2006 - 08:50 AM

Was This Post Helpful? 1

#7 Mystrunner  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 24
  • Joined: 07-July 06

Re: Running Command Prompt in VB/VBA

Posted 07 July 2006 - 09:02 AM

Here's my current code:


Private Sub btnSubmit_Click()

Dim strURL, strUserName, strPassword As String
Dim Shell, IE, readHTML, readText

On Error GoTo ErrHandler
Dim objHTTP As HTTP
Const conTARGET = "~~~~~~~~Address Here~~~~~~~~~"

  Set objHTTP = New HTTP
  With objHTTP
	.About
	.HttpURL = conTARGET
	'.DestinationFile = "c:\temp\UpdateStorage.txt"
	.PromptWithCommonDialog = True
	If .FileExists Then .OverwriteTarget = True
	If Not .IsConnected Then .DialDefaultNumber
	.ConnectToHTTPHost
	.WriteHTTPDataToFile
  End With
ExitHere:
  On Error Resume Next
  Set objHTTP = Nothing
  Call SysCmd(acSysCmdRemoveMeter)
  Exit Sub
ErrHandler:
  MsgBox Err.Number & vbCrLf & Err.Description, _
		 vbCritical + vbOKOnly, Err.Source
  Resume ExitHere


Dim filesys, newfolder
Dim newfolderpath As String
newfolderpath = "c:\Temp"
Set filesys = CreateObject("Scripting.FileSystemObject")

If filesys.FolderExists(newfolderpath) <> True Then
	Set newfolder = filesys.CreateFolder(newfolderpath)
End If

Dim oFilesys, oFiletxt, sFilename, sPath
Set oFilesys = CreateObject("Scripting.FileSystemObject")
Set oFiletxt = oFilesys.CreateTextFile("c:\Temp\UpdateStorage.txt", True)
sPath = oFilesys.GetAbsolutePathName("c:\Temp\UpdateStorage.txt")
sFilename = oFilesys.getFileName(sPath)
oFiletxt.WriteLine (test)
oFiletxt.Close '



   'declare working variables
	Dim fno As Integer, fname As String


   'obtain the next free file handle from the system
	fno = FreeFile
	fname = "c:\Temp\UpdateStorage.txt"


   'open and save the file from the textbox
	Open fname$ For Output As #fno
		Print #fno, (readText)
	Close #fno
	
	'docmd.TransferText(acImportDelim,,

MsgBox ("Update successful.")
End Sub



I was originally trying to make this thing work via HTTP, but I ran into trouble due to the fact that the HTTP we use is password protected, and I'm not sure as to how to submit a username and password through VBA.

---Update---

Found a snippit of code that pulls up the FTP portion of the CMD prompt, using this code:

Dim stSysDir As String
	stSysDir = Environ$("COMSPEC")
	stSysDir = Left$(stSysDir, Len(stSysDir) - Len(Dir(stSysDir)))
	Call Shell(stSysDir & "cmd.exe -s:" & stSCRFile, vbNormalFocus)



All I need to do now is figure out how it works... >_>;

This post has been edited by Mystrunner: 07 July 2006 - 09:12 AM

Was This Post Helpful? 0
  • +
  • -

#8 Jayman  Icon User is offline

  • Student of Life
  • member icon

Reputation: 418
  • View blog
  • Posts: 9,532
  • Joined: 26-December 05

Re: Running Command Prompt in VB/VBA

Posted 07 July 2006 - 03:21 PM

View PostMystrunner, on 7 Jul, 2006 - 07:54 AM, said:

Dim stSysDir As String
	stSysDir = Environ$("COMSPEC")
	stSysDir = Left$(stSysDir, Len(stSysDir) - Len(Dir(stSysDir)))
	Call Shell(stSysDir & "cmd.exe -s:" & stSCRFile, vbNormalFocus)


All I need to do now is figure out how it works... >_>;

You are on the right track. This code determines the path to your system folder. You don't need to mess with this.
	stSysDir = Environ$("COMSPEC")
	stSysDir = Left$(stSysDir, Len(stSysDir) - Len(Dir(stSysDir)))


Now this code opens the command window and passes some arguments to it. "cmd.exe -s:" & stSCRFile this is the command and its argument. If you need to pass arguments then just put them into a string and concatenate with the & symbol.
	Call Shell(stSysDir & "cmd.exe -s:" & stSCRFile, vbNormalFocus)


Now I found this bit of code using the ftp window just as you are so it might help.
Public Sub RunFTP(ByVal strScriptFile As String)

Dim strQuote As String

strQuote = Chr(34)

strWinSysDir = Environ("COMSPEC")
strWinSysDir = Left(strWinSysDir, Len(strWinSysDir) - Len(Dir(strWinSysDir)))
Call Shell(strWinSysDir & "ftp.exe -s:" & strQuote & strScriptFile & strQuote, vbNormalNoFocus)
End Sub


Was This Post Helpful? 0
  • +
  • -

#9 stranger_6_7  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 14-July 06

Re: Running Command Prompt in VB/VBA

Posted 14 July 2006 - 03:39 AM

Hi

I am working on a php application. I have a query that is similar to the one posted regarding access to applications.

I have a button in one my pages in which , whenever any user clicks on that html button , it should automatically open command prompt,

I used WScript and Shell.Run ...... etc in Javascript and by changing security settings on IE , i was finally able to open command prompt automatically by click of that button.

But this approach is browser dependant and is not working on other browsers where ActiveX support or WSH support is not provided.

Can anyone help me how to fix this problem or post if there is any other way to run command prompt from windows.

Totally, i need to use the same code to run a shell when my application runs under linux.

Thanks for the help in Advance

Stranger.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1