First of all, I think the problem is that you don't know what the "Current"
directory is for your Excel instance.
When you execute the .bat file manually,
You're probably using the keystrokes:
C:\
cd \Users\User\Desktop\mails\Rename\
Extract
The portion of the command "> myfiles.txt"
redirects the output of the command to the file IN THE CURRENT DIRECTORY.
The problem is, in your excel session, you don't know what that is!
so, you can do this one of two ways:
In VBA:
Dim strBatchName As String
chdir "C:\Users\User\Desktop\mails\Rename\"
strBatchName = "C:\Users\User\Desktop\mails\Rename\Extract.bat"
Shell strBatchName
End sub
or change your .bat file:
dir /b "C:\Users\User\Desktop\mails" >
C:\Users\User\Desktop\mails\Rename\myFiles.txt
====================================================
Secondly, what is the purpose?
If you're trying to get the list of the contents of the
"C:\Users\User\Desktop\mails" folder, this method is a bit convoluted...
You're using a VBA program to execute a Command Shell, in which you run a DOS
command to list the contents of the File System and redirect the output to an
external file.
Instead, you could use VBA's Filesystem object and list the contents of the
file.
If the end result you wish to achieve is a file listing of the content, then by
all means, write it to a text file.
But if you're intending to bring the list into Excel, then you could write it
from that point.
the code to write the list to a text file is:
Sub Test()
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fLog, LogFile
Dim fso, f, fld, sf, fc, fldr
Dim StrPath As String
Set fso = CreateObject("Scripting.FileSystemObject")
LogFile = "C:\temp\MyFiles.txt"
Set fLog = fso.OpenTextFile(LogFile, ForWriting, True)
StrPath = "C:\Users\User\Desktop\mails"
If (fso.folderexists(StrPath)) Then
Set fldr = fso.GetFolder(StrPath)
Set sf = fldr.SubFolders
If (sf.Count > 0) Then
For Each fld In sf
fLog.writeline fld.Name
Next fld
End If
Set fc = fldr.Files
For Each f In fc
fLog.writeline f.Name
Next f
End If
fLog.Close
End Sub
============================================
Thirdly, it is ill-advised (not recommended) that you call a subroutine "Run"
(as in Sub Run() )
Run is an Excel Application method.
by calling a user-defined subroutine the same name as a standard method,
depending on where you place it and where you are when you run it, you could
execute your macro, or Excel's, with unpredictable results.
hope this helps.
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
>________________________________
> From: gargee singh <garge...@hotmail.com>
>To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
>Sent: Wednesday, January 15, 2014 9:22 AM
>Subject: $$Excel-Macros$$ batch file Query
>
>
>
>
>Hi All,
>
>I am trying to run a batch file from a VBA code. Both the excel file and the
>batch files are placed in the same folder.
>However the batch file does not extract names when I run it from VBA, if I do
>it manually then It works fine.
>
>Mentioned below are the codes, what could be the issue ?
>
>Batch file :-
>dir /b "C:\Users\User\Desktop\mails" > myFiles.txt
>
>this batch file names for all the files in the current folder and places them
>in a text file named – “ myFiles”
>
>Vba in excel -
>
>
>
>
>sub run()
>Dim strBatchName As String
>strBatchName = "C:\Users\User\Desktop\mails\Rename\Extract.bat"
>Shell strBatchName
>End sub
>
>Best regards
>gargee
>
>
>
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
