MS Office Forum / Word / Programming / January 2006
call to a DOS command from VBA
|
|
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.
|
|
|