Hello Paul,
Thanks for your help with this , this has been more than just helpful. This
was exactly what i needed.
regarding the 3rd point , i am aware that RUN is an excel Application method
and not appropriate for a subroutine name, but i was so tired of not being able
to run the batch file , that I thought may be this was the only missing piece
in the code. That if i named the Subroutine RUN, it will run the batch file.
:)
not a very intelligent thought i know. :)
Thanks once again.
Best RegardsGargee
Date: Wed, 15 Jan 2014 08:07:36 -0800
From: schreiner_p...@att.net
Subject: Re: $$Excel-Macros$$ batch file Query
To: excel-macros@googlegroups.com
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\ExtractThe 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 Stringchdir
"C:\Users\User\Desktop\mails\Rename\"strBatchName =
"C:\Users\User\Desktop\mails\Rename\Extract.bat"Shell strBatchNameEnd 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 StringstrBatchName =
"C:\Users\User\Desktop\mails\Rename\Extract.bat"Shell strBatchNameEnd sub
Best regardsgargee
--
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.
--
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.
