getting vba shell command to execute batch and save output to a file

I have a batch file I would like to execute from an Excel macro, and
have the output be piped to a file. For this test I created a file
"dir.bat" in the same folder as "Book1.xls", the file contains one
line, "dir /b /o"

>From the command prompt, if I run it like so

dir.bat > dir.txt

it successfully saves the directory listing to "dir.txt".

However it doesn't seem to be working from the Excel macro:

Sub ShellTest()
Shell ThisWorkbook.Path & "\dir.bat > dir.txt"
End Sub

I get no error messages, and dir.txt gets created, but it is empty.

Can anyone see anything wrong?

Much appreciated...

why not change the line in the bat file to

"dir /b /o > dir.txt"

--
Regards,
Tom Ogilvy


"Mad Scientist Jr" wrote:

> I have a batch file I would like to execute from an Excel macro, and
> have the output be piped to a file. For this test I created a file
> "dir.bat" in the same folder as "Book1.xls", the file contains one
> line, "dir /b /o"
>
> >From the command prompt, if I run it like so
>
> dir.bat > dir.txt
>
> it successfully saves the directory listing to "dir.txt".
>
> However it doesn't seem to be working from the Excel macro:
>
> Sub ShellTest()
> Shell ThisWorkbook.Path & "\dir.bat > dir.txt"
> End Sub
>
> I get no error messages, and dir.txt gets created, but it is empty.
>
> Can anyone see anything wrong?
>
> Much appreciated...
>
>

"Why didn't I think of that?!"
Sounds like a good idea, let me try that, thanks!

Tom Ogilvy wrote:
> why not change the line in the bat file to
>
> "dir /b /o > dir.txt"
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Mad Scientist Jr" wrote:
>
> > I have a batch file I would like to execute from an Excel macro, and
> > have the output be piped to a file. For this test I created a file
> > "dir.bat" in the same folder as "Book1.xls", the file contains one
> > line, "dir /b /o"
> >
> > >From the command prompt, if I run it like so
> >
> > dir.bat > dir.txt
> >
> > it successfully saves the directory listing to "dir.txt".
> >
> > However it doesn't seem to be working from the Excel macro:
> >
> > Sub ShellTest()
> > Shell ThisWorkbook.Path & "\dir.bat > dir.txt"
> > End Sub
> >
> > I get no error messages, and dir.txt gets created, but it is empty.
> >
> > Can anyone see anything wrong?
> >
> > Much appreciated...
> >
> >