Issuing DOS command in VBA macro?
 

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.

Issuing DOS command in VBA macro?
 

Use Shell:

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

--
Jim Rech
Excel MVP
"Don Wiss" wrote in message
...
| 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.

Issuing DOS command in VBA macro?
 

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
-------------------

Change the wait time to a larger number if the .prn file is not gettin
created fast enough.



--
Message posted from http://www.ExcelForum.com

Issuing DOS command in VBA macro?
 

"Jim Rech" wrote...
Use Shell:

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

Where would Temp.prn be located?

--
To top-post is human, to bottom-post and snip is sublime.

Issuing DOS command in VBA macro?
 

On Wed, 5 May 2004 14:38:06 -0400, "Jim Rech" wrote:

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.

Issuing DOS command in VBA macro?
 

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).

--
Regards,
Tom Ogilvy

"Don Wiss" wrote in message
...
On Wed, 5 May 2004 14:38:06 -0400, "Jim Rech" wrote:

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.

Issuing DOS command in VBA macro?
 

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

"Don Wiss" wrote in message
...
| On Wed, 5 May 2004 14:38:06 -0400, "Jim Rech" wrote:
|
| 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.

Issuing DOS command in VBA macro?
 

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

--
Don Guillett
SalesAid Software

"Don Wiss" wrote in message
...
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.

Issuing DOS command in VBA macro?
 

"Jim Rech" wrote...
Take out the extra quotes you added:

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

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.

--
To top-post is human, to bottom-post and snip is sublime.

Issuing DOS command in VBA macro?
 

On Thu, 6 May 2004 08:25:42 -0500, Don Guillett wrote:

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.

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.

Issuing DOS command in VBA macro?
 

On Wed, 5 May 2004 23:02:25 -0400, Tom Ogilvy wrote:

Don Wiss wrote:
On Wed, 5 May 2004 14:38:06 -0400, "Jim Rech" wrote:

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.

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).

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.

Issuing DOS command in VBA macro?
 

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

--
Don Guillett
SalesAid Software

"Don Wiss" wrote in message
...
On Thu, 6 May 2004 08:25:42 -0500, Don Guillett wrote:

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.

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.