Issuing DOS command in VBA macro?

  • Thread starter Thread starter Don Wiss
  • Start date Start date
D

Don Wiss

To read files from my hard disk into a spreadsheet I run a batch file like:

dir "G:\My Music\Comedy\*.*" >c:\temp.prn
pause

dir "G:\My Music\Compilations\*.*" >c:\temp.prn
pause

dir "G:\My Music\Country\*.*" >c:\temp.prn
pause

Then at each pause I change the focus to the spreadsheet and type Ctrl-H,
which runs the import macro. It would simplify things greatly if I could
issue the commands in a macro and have all run in one step. I know issuing
a command is easy in regular programming languages. What is involved to do
so in VBA? (I'm using Excel 2002.)

Don <donwiss at panix.com>.
 
Use Shell:

Shell "command.com /c DIR c:\*.* > Temp.prn"

--
Jim Rech
Excel MVP
| To read files from my hard disk into a spreadsheet I run a batch file
like:
|
| dir "G:\My Music\Comedy\*.*" >c:\temp.prn
| pause
|
| dir "G:\My Music\Compilations\*.*" >c:\temp.prn
| pause
|
| dir "G:\My Music\Country\*.*" >c:\temp.prn
| pause
|
| Then at each pause I change the focus to the spreadsheet and type Ctrl-H,
| which runs the import macro. It would simplify things greatly if I could
| issue the commands in a macro and have all run in one step. I know issuing
| a command is easy in regular programming languages. What is involved to do
| so in VBA? (I'm using Excel 2002.)
|
| Don <donwiss at panix.com>.
 
Why not create your batch files on the fly, execute them and then cal
the import routine from the same code...

Code
-------------------
Sub BatFileCreateAndImport()

Dim datTemp As Date

Close
Open "C:\Test123.bat" For Output As #1
Print #1, "dir ""G:\My Music\Comedy\*.*"" >c:\temp.prn"
Close

datTemp = Now() + 1 / 24 / 60 / 60 'Plus 1 second
Do Until Now() > datTemp
Loop

'call procedure to run import call here

Close
Open "C:\Test123.bat" For Output As #1
Print #1, "dir ""G:\My Music\Compilations\*.*"" >c:\temp.prn"
Close

datTemp = Now() + 1 / 24 / 60 / 60 'Plus 1 second
Do Until Now() > datTemp
Loop

'call procedure to run import call here

'Etcetera...

End Su
 
Use Shell:

Shell "command.com /c DIR c:\*.* > Temp.prn"
Click to expand...

Okay. I modified it to following one of mine. It became:

Shell "command.com /c dir ""G:\My Music\Country\*.*"" > c:\temp.prn"

Trying in the Immediate window it runs, but I'm left with a 0 byte file.

Don <donwiss at panix.com>.
 
In help on the shell function it says:

Note The Shell function runs other programs asynchronously. This means
that a program started with Shell might not finish executing before the
statements following the Shell function are executed.

You might need to use the wait command to allow the shell function to
finish. (similar to your use of pause).
 
Take out the extra quotes you added:

Sub Test()
Shell "command.com /c dir G:\My Music\Country\*.* > c:\temp.prn"
End Sub

--
Jim Rech
Excel MVP

|
| >Use Shell:
| >
| >Shell "command.com /c DIR c:\*.* > Temp.prn"
|
| Okay. I modified it to following one of mine. It became:
|
| Shell "command.com /c dir ""G:\My Music\Country\*.*"" > c:\temp.prn"
|
| Trying in the Immediate window it runs, but I'm left with a 0 byte file.
|
| Don <donwiss at panix.com>.
 
If you are trying to make a list of files from a directory to a worksheet,
why not use filesearch? Here are a couple of examples.

Sub GetFileList2222()
Dim iCtr As Integer

With Application.FileSearch
.NewSearch
.LookIn = "c:\aa"
.SearchSubFolders = True
.Filename = ".xls"
If .Execute > 0 Then
For iCtr = 1 To .FoundFiles.Count
Cells(iCtr, 1).Value = .FoundFiles(iCtr)
Next iCtr
End If
End With
End Sub

Sub FindandListFiles()
Application.ScreenUpdating = False
Columns(1).ClearContents
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim FileLocation As String
FileLocation = "c:\keystone\*.xls"
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub
 
Take out the extra quotes you added:

Sub Test()
Shell "command.com /c dir G:\My Music\Country\*.* > c:\temp.prn"
End Sub
Click to expand...

I guarantee you this won't produce the correct results. DOS, meaning either
COMMAND.COM or CMD.EXE, kinda requires double quotes around long filenames with
embedded spaces. Your command above is equivalent to

dir G:\My

dir .\Music\Country\*.*

The command *MUST* be

dir "G:\My Music\Country\*.*"

in order to work.
 
If you are trying to make a list of files from a directory to a worksheet,
why not use filesearch? Here are a couple of examples.
Click to expand...

I don't believe it will also produce the file size and date as a Dir will.
Plus my import macro expects to find the volume label when the dir comes
from a CDR, and I extract the directory name when the dir comes from a
folder. And I need all file types.

Don <donwiss at panix.com>.
 
In help on the shell function it says:

Note The Shell function runs other programs asynchronously. This means
that a program started with Shell might not finish executing before the
statements following the Shell function are executed.

You might need to use the wait command to allow the shell function to
finish. (similar to your use of pause).
Click to expand...

But Tom. I have only tested it so far in the Immediate window. And it only
produces a 0 byte file. Wait. I just tried:

Shell "cmd.exe /c dir ""G:\My Music\Country\*.*"" > c:\temp.prn"

And it works! Now to write the cover code to loop among the directories.
I'll put the folder names in an array.

Don <donwiss at panix.com>.
 
Here is part of one I use to get .mp3 files that does list the file size,
length, date time. See if helps.

Sub FindFiles()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
musicpath = use your path here
'Finds Files
With Application.FileSearch
.NewSearch
.LookIn = musicpath
.SearchSubFolders = True 'False
.MatchTextExactly = False
..filename=*.* 'changed from mine below
' .Filename = ".mp3" '*.mp3* did not work in 97
If .Execute(msoSortOrderDescending) > 0 Then
'MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
'MsgBox Mid(.FoundFiles(i), Len(musicpath) + 1, 2)
If Mid(.FoundFiles(i), Len(musicpath) + 1, 2) <> "__" Then 'added for
__INCOMPLETES

x = Application.Find("\", StrReverse(.FoundFiles(i))) - 2 'must have
function before xl2000
y = Application.Find("-", StrReverse(.FoundFiles(i))) - 1

Cells(i + lastrow, 1).Value = Mid(.FoundFiles(i), Len(.FoundFiles(i)) - x,
x - y)
x = Application.Find("-", .FoundFiles(i)) + 1
Cells(i + lastrow, 2).Value = Mid(.FoundFiles(i), x, Len(.FoundFiles(i)) -
x - 3)
Cells(i + lastrow, 3).Value = FileLen(.FoundFiles(i))
Cells(i + lastrow, 4).Value = FileDateTime(.FoundFiles(i))
Cells(i + lastrow, 5).Value = .FoundFiles(i) 'Path to play
End If 'added
Next i

Else
MsgBox "There were no files found."
End If
End With

Range("a5:g" & Range("a65536").End(xlUp).Row) _
..Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2),
Order2:=xlAscending, Orientation:=xlTopToBottom
[a5].Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 
Back
Top