Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / January 2006

Tip: Looking for answers? Try searching our database.

call to a DOS command from VBA

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JanAdam - 15 Jan 2006 23:10 GMT
I am new to VBA I need help with calling a DOS command from within a VBA 6
code. Here is an example: Suppose I need a list of files in a folder. One can
do it using DOS dir command like

Prompt:> dir drive:\path\foldername > drive:\path\list.txt

The file list.txt output file created contains all file names in foldername,
plus some other info. I have written a vba code for Word to *clean* the
list.txt file, such that it now only contains the names of files in the
foldername folder. The question I have is: can one write a stand alone
*application* or a Word addin, such that it starts with a user form in which
one can browse to a foldername as a source and say cleanlist.doc as the
output, and on closing the form, the dir command is executed, the list.txt
file cleaned with my code, and the cleaned output file placed into a
path\cleanedfile.doc file? I know how to open, create and close files from a
vba code. I do not know how to call a dos command from vba and how to make a
form with controls that allow to browse for a source and destination folders.
Any reading suggestions? Thanks,

Signature

JanAdam

Anne Troy - 16 Jan 2006 07:08 GMT
Perhaps this isn't exactly what you want, but perhaps it will give you
enough to get where you want to be?
http://vbaexpress.com/kb/getarticle.php?kb_id=591

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

>I am new to VBA I need help with calling a DOS command from within a VBA 6
> code. Here is an example: Suppose I need a list of files in a folder. One
[quoted text clipped - 20 lines]
> folders.
> Any reading suggestions? Thanks,
Helmut Weber - 16 Jan 2006 13:51 GMT
Hi Adam,

like this:

Dim x
x = Shell("cmd /c dir c:\ > c:\test\output.txt")

--
Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000
Steve Yandl - 16 Jan 2006 14:53 GMT
JanAdam,

Using the Shell command to run the command console dir command works but the
rest of your code will not wait for the operation to complete so you may
have code trying to clean up a text file that isn't yet created.  I prefer
to use the filesystemobject (Anne's link).  If you do use Shell, the example
Helmut offered will work unless you're on a Win98 machine where command.com
is used rather than cmd.exe.  Below is an example that shows one way to get
a folder browse window and it uses "dir" for any Windows OS but it still
might have timing issues when you add your code to "tidy" up the text file
output.

The user selects a folder in the browse for folder window, clicks OK and the
contents of that folder are output as C:\myLog.txt.  The browse window is
set to show "C:\" as the top level folder.

Sub FolderContents()
Dim objPath As String
Const WINDOW_HANDLE = 0
Const NO_OPTIONS = 0
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder _
   (WINDOW_HANDLE, "Select a folder:", NO_OPTIONS, "C:\")
Set objFolderItem = objFolder.Self
objPath = Chr(34) & objFolderItem.Path & "\" & Chr(34)
Shell Environ$("comspec") & " /c dir " & objPath & " > C:\myLog.txt", vbHide
End Sub

Steve

>I am new to VBA I need help with calling a DOS command from within a VBA 6
> code. Here is an example: Suppose I need a list of files in a folder. One
[quoted text clipped - 20 lines]
> folders.
> Any reading suggestions? Thanks,
Helmut Weber - 16 Jan 2006 15:51 GMT
Hi everybody,

just in case somebody needs "shell" for whatever reason,
and has to wait until the command has been completed.

http://vb-tec.de/xshell.htm

Unfortunatly, this page is in german.

By the way, the function is called "shellx",
not "xshell", as the address indicates.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

JanAdam - 16 Jan 2006 16:31 GMT
Thank you all for your help. It will keep me busy for a while.
Would *DoEvents* after Helmut’s Shell command take care
of the timing issues?
Helmut: unfortunately my German is barely good enough to
order a beer but is by far too limited for a technical text.

Signature

JanAdam

> Hi everybody,
>
[quoted text clipped - 7 lines]
> By the way, the function is called "shellx",
> not "xshell", as the address indicates.
Helmut Weber - 16 Jan 2006 17:22 GMT
Hi JanAdam,
just copy and paste it and see what it will do.

' ------------------------------------------------------
Option Explicit
Private Declare Function CloseHandle Lib "kernel32" ( _
   ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
   ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" ( _
   ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
   ByVal dwProcessId As Long) As Long
Public Function ShellX( _
   ByVal PathName As String, _
   Optional ByVal WindowStyle As VbAppWinStyle = vbMinimizedFocus, _
   Optional ByVal Events As Boolean = True _
 ) As Long

 'Declarations:
 Const STILL_ACTIVE = &H103&
 Const PROCESS_QUERY_INFORMATION = &H400&
 Dim ProcId As Long
 Dim ProcHnd As Long

 'Get process-handle:
 ProcId = Shell(PathName, WindowStyle)
 ProcHnd = OpenProcess(PROCESS_QUERY_INFORMATION, True, ProcId)

 'wait for process end:
 Do
   If Events Then DoEvents
   GetExitCodeProcess ProcHnd, ShellX
 Loop While ShellX = STILL_ACTIVE

 'clean up:
 CloseHandle ProcHnd

End Function
Sub testdunno()
Dim x As Long
x = ShellX("cmd /c dir c:\ / s > c:\test\output.txt")
Beep ' done
End Sub

' -------------------------------------------------------

It isn't that I understand what's going on.
But got it to work.
Might need some minutes.
Live and learn.

In fact, it ran for more than 8 hours
to record all that was on a server,
with different paths, of course.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Steve Yandl - 16 Jan 2006 19:58 GMT
JanAdam,

See if this doesn't get you close to what you want to do, even though it
doesn't use dir.

Sub FolderBrowse()
Dim objPath As String
Dim docNew As Word.Document
Const WINDOW_HANDLE = 0
Const NO_OPTIONS = 0

On Error Resume Next
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder _
   (WINDOW_HANDLE, "Select a folder:", NO_OPTIONS, "C:\")
Set objFolderItem = objFolder.Self
objPath = objFolderItem.Path
Err.Clear

Set fso = CreateObject("Scripting.FileSystemObject")

If Len(objPath) > 0 Then
Set docNew = Documents.Add

With docNew
Set fdrFolder = fso.GetFolder(objPath)
  If fdrFolder.Files.Count > 0 Then
  Selection.TypeText "Files contained in " & objPath
  Selection.TypeParagraph
  For Each filFile In fdrFolder.Files
  Selection.TypeText fso.GetFileName(filFile)
  Selection.TypeParagraph
  Next filFile
  Else
  Selection.TypeText "No files in " & objPath
  Selection.TypeParagraph
  End If

  If fdrFolder.Subfolders.Count > 0 Then
  Selection.TypeText "Subfolders contained in " & objPath
  Selection.TypeParagraph
  For Each fdrSubFolder In fdrFolder.Subfolders
  Selection.TypeText fso.GetFileName(fdrSubFolder)
  Selection.TypeParagraph
  Next fdrSubFolder
  Else
  Selection.TypeText "No subfolders in " & objPath
  Selection.TypeParagraph
  End If

End With

Else
Exit Sub
End If

Set objShell = Nothing
Set fso = Nothing

End Sub

Steve

> Thank you all for your help. It will keep me busy for a while.
> Would *DoEvents* after Helmut's Shell command take care
[quoted text clipped - 13 lines]
>> By the way, the function is called "shellx",
>> not "xshell", as the address indicates.
JanAdam - 16 Jan 2006 20:07 GMT
Thanks again Helmut, or can I say Viel Dank?

I will try it. By the way, reading the page you suggested was not that
difficult as thought it would be. I think I can make most of it. I see that
you repeat DoEvents in a loop until ShellX is done. Nice.

Steve, thank you too. It is very nice to receive so much support and sound
advice.

Much appreciated.

Signature

JanAdam

> JanAdam,
>
[quoted text clipped - 76 lines]
> >> By the way, the function is called "shellx",
> >> not "xshell", as the address indicates.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.