call to a DOS command from VBA

[JanAdam]

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,

 

[Anne Troy]

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

 

[Helmut Weber]

Hi Adam,

like this:

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

 

[Steve Yandl]

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

 

[Helmut Weber]

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.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[JanAdam]

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.

 

[Helmut Weber]

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.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

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

 

[Steve Yandl]

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

 

[JanAdam]

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.