Instructing Dos to take a formula from a cell within excel and excecuteing it

[proditneg]

Hi,

I am having a difficulty on writing a command within excel so that a set of formulas on a given column is excecuted in dos.

this is the formula which i want to be excecuted from within excel:

md "D:\location\folder name " & xcopy "S:\location\filename" "D:\location\folder name" & Rename "D:\location\filename" "new filename"

this formula runs accros the column depending on the number of entries.

If anyone can advice that would be extremley helpful as im stuck at this point.

Cheers

 

[Ruddles]

Please do check Taurean's link as you might find that does exactly what you want. (I haven't looked at it.)

Otherwise, if you just want to pass all the contents of column U to DOS one line at a time, you can do this:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub ExecDOScommands()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim ws As Worksheet
  Dim iLastRow As Long
  Dim iRow As Long
  
  Set ws = ThisWorkbook.Sheets(1)
  iLastRow = ws.Cells(ws.Rows.Count, "U").End(xlUp).Row
  
  For iRow = 2 To iLastRow
    Shell Environ("COMSPEC") & " /c " & ws.Cells(iRow, "U").Value
  Next iRow[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Sub[/FONT]

 

[proditneg]

Ruddles,

Thanks for the advice i`v implemented it and it does work. Checked Taurean's link but although that was very similar to what i want to achive it didnt seem to work.
Although i am happy with the code one last thing would benefit the overall process. I find it difficult to realise when all the commands have finished excecuting. Is there a way so that when the last line of the command is excecuted i could get a notification.
Cheers

 

[Ruddles]

Strictly speaking not, because when you Shell out to DOS, the DOS shell runs asynchronously to VBA - that is, the VBA initiates the DOS shell and then immediately proceeds to the next VBA statement, leaving the DOS shell to run in its own time.

So when the final Shell command executes, the VBA macro ends immediately, leaving the final DOS shell running.

Do these DOS commands each take a very long time to run then? If not, just place a MsgBox command at the end of the macro.

If they do each take a very long time to run and you actually want to know when the final shell terminates, then there's a version of Shell called ShellWait which will wait for the shell to terminate before it proceeds.

 

[taurean]

"Bat" way of doing things. Try the following code:

Option Explicit
Const csBat As String = "C:\Execute.bat"
Const csScript As String = "C:\Clear.vbs"
Public Sub ExecuteBatFile()
Dim lLastRow As Long, i As Long
Dim FSO As Object, Batfile As Object
Dim rShell
lLastRow = Range("U" & Rows.Count).End(xlUp).Row
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Batfile = FSO.CreateTextFile(csBat, True)
For i = 2 To lLastRow
Batfile.WriteLine Range("U" & i).Value2
Next i
Batfile.WriteLine csScript
Batfile.Close
CreateVBScript
rShell = Shell(csBat, vbHide)
End Sub
Private Sub CreateVBScript()
Dim FSO As Object, Scriptfile As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Scriptfile = FSO.CreateTextFile(csScript, True)
'vbcrlf = vb carriage & linefeed; Chr(34) = "" (double quotes)
Scriptfile.WriteLine "Set objFSO = CreateObject(" & Chr(34) & _
"Scripting.FileSystemObject" & Chr(34) & ")" & vbCrLf & _
"Msgbox " & Chr(34) & "Commands Executed in DOS!" & Chr(34) & vbCrLf & _
"objFSO.DeleteFile " & Chr(34) & csBat & Chr(34) & vbCrLf & _
"objFSO.DeleteFile WScript.ScriptFullName" & vbCrLf & _
"WScript.Quit"
End Sub

Code:
1. This creates two file Execute.bat and Clear.vbs.
2. Execute.bat contains all your commands in Column U.
3. After Executing all commands it calls Clear.vbs
4. Clear.vbs is self destruct vbscript code which deletes Execute.bat first and then raises the message that the task is finishes and then kills itself.

 

[proditneg]

Hi there Rudles,

The code you have provided will most likely do the job it implies, but because i want to excecute a formula (Location column U) whose information is based on the results of the information gathered from a number of cells, the only thing to work in this case will be a way to instruct dos to excecute that formula. I have attached a screen of the column which contains the commands.

What i need is to find a way through which excel will look into a range of cells (column U), copies information from those cells, communicates with dos and instructs dos to excecute that information.

Cheers

"Bat" way of doing things. Try the following code:

Option Explicit
Const csBat As String = "C:\Execute.bat"
Const csScript As String = "C:\Clear.vbs"
Public Sub ExecuteBatFile()
Dim lLastRow As Long, i As Long
Dim FSO As Object, Batfile As Object
Dim rShell
lLastRow = Range("U" & Rows.Count).End(xlUp).Row
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Batfile = FSO.CreateTextFile(csBat, True)
For i = 2 To lLastRow
Batfile.WriteLine Range("U" & i).Value2
Next i
Batfile.WriteLine csScript
Batfile.Close
CreateVBScript
rShell = Shell(csBat, vbHide)
End Sub
Private Sub CreateVBScript()
Dim FSO As Object, Scriptfile As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Scriptfile = FSO.CreateTextFile(csScript, True)
'vbcrlf = vb carriage & linefeed; Chr(34) = "" (double quotes)
Scriptfile.WriteLine "Set objFSO = CreateObject(" & Chr(34) & _
"Scripting.FileSystemObject" & Chr(34) & ")" & vbCrLf & _
"Msgbox " & Chr(34) & "Commands Executed in DOS!" & Chr(34) & vbCrLf & _
"objFSO.DeleteFile " & Chr(34) & csBat & Chr(34) & vbCrLf & _
"objFSO.DeleteFile WScript.ScriptFullName" & vbCrLf & _
"WScript.Quit"
End Sub

Code:
1. This creates two file Execute.bat and Clear.vbs.
2. Execute.bat contains all your commands in Column U.
3. After Executing all commands it calls Clear.vbs
4. Clear.vbs is self destruct vbscript code which deletes Execute.bat first and then raises the message that the task is finishes and then kills itself.

Hi Taurean,

Much appreciated. I spend yesterday most of the afternooon till late evening trying to figure out the shellwait command with no success. Your code works like a charm. Thanks a lot!
The reason why your previous code from the other forum did not seem to work for me is that i am working with files which already exist on different locations and before collecting them i want to create a directory with a new name, copy those files in this directory and rename them and number them sequentially (01_name.ext, 02_name.ext) keeping their original extension .
I understand that a code writen in VBA would be more robust and handle tasks probably faster, but I couldnt manage to modify your code so that i could adopt it to what i need. Are you able may be to modify you other code so that it can handle the same commands in VBA.

cheers

 

[taurean]

Yes, the VBA way will be different from the DOS + VBSCRIPT handling as it has built-in capabilities for this. I have few doubts:

I suppose you have all the files listed (which are to be copied to one folder). Do you want VBA to assign an incremental name or you have them defined as well?

You can post a dummy sample of the worksheet (Use HTML maker) so that we'll understand the way you are setting out to do the task and then you will get better help (not just me) from us.

 

[proditneg]

At the moment working from the dos command i populate 4 columns:

* New folder name (for dos to create a folder before copying the files into)
* Source file location+name+extension (for dos to copy files into the above created folder)
* Existing file name (for dos to know which file needs to be renumbered and renamed)
* New file number (01_, 02_ etc, for dos to renumber those files in the way instructed)
* New file name (for dos to put the new file name as instructed, (the last commands can be merged together))

At the moment this info is sufficient for dos commands to carry out the following: create a new folder (as instructed) copy files into that folder and rename them as instructed.

However i was thinking that a VBA code would speed up processes as the number of files involved is in thousands and because i am working with audio files they are significantly large.

thanks

 

[taurean]

Few things before we proceed:

1. I am not sure if VBA code will give speed advantage over BAT + SCRIPT file mechanism. I do not have such deeper understanding of these matters.

2. The advantage I saw in DOS + SCRIPT method was it would free up Excel as soon as it wrote BAT and SCRIPT file (which would be almost immediate). Then on it would run in the background at the end of process, you will be notified when the activity finishes (which is possible in VBA also).

3. To write VBA Code I have assumed following which you can adjust:
Column Layout:
<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 121px"><COL style="WIDTH: 564px"><COL style="WIDTH: 123px"><COL style="WIDTH: 265px"><COL style="WIDTH: 281px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>New Folder Name</TD><TD>Source File Location + Name + Extension</TD><TD>Existing File Name</TD><TD>New File Number</TD><TD>New File Name</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>C:\Test</TD><TD>D:\songs\Murder 2\01_Hale_Dil.mp3</TD><TD></TD><TD></TD><TD>Hale_Dil.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD></TD><TD>D:\songs\Murder 2\Aa-Zara-(Muskurahat.Com).mp3</TD><TD></TD><TD></TD><TD>Aa-Zara.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD></TD><TD>D:\songs\Murder 2\Aye-Khuda-(Muskurahat.Com).mp3</TD><TD></TD><TD></TD><TD>Aye-Khuda.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD></TD><TD>D:\songs\Murder 2\Phir-Mohabbat-(Muskurahat.Com).mp3</TD><TD></TD><TD></TD><TD>Phir-Mohabbat.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD></TD><TD>D:\songs\Murder 2\Tujhko-Bhulaana-(Muskurahat.Com).mp3</TD><TD></TD><TD></TD><TD>Tujhko-Bhulaana.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>C:\Test\Test1</TD><TD>D:\songs\2008\SONG\songs 2006\02 Don\01 Main Hoon Don.mp3</TD><TD></TD><TD>01 Main Hoon Don</TD><TD>Main Hoon Don.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD></TD><TD>D:\songs\2008\SONG\songs 2006\02 Don\02 Ye Mera Dil.mp3</TD><TD></TD><TD>02 Ye Mera Dil</TD><TD>Ye Mera Dil.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD></TD><TD>D:\songs\2008\SONG\songs 2006\02 Don\03 Maurya Re.mp3</TD><TD></TD><TD>03 Maurya Re</TD><TD>Maurya Re.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD></TD><TD>D:\songs\2008\SONG\songs 2006\02 Don\04 Khaike Paan.mp3</TD><TD></TD><TD>04 Khaike Paan</TD><TD>Khaike Paan.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD></TD><TD>D:\songs\2008\SONG\songs 2006\02 Don\05 Aaj Ki Raat.mp3</TD><TD></TD><TD>05 Aaj Ki Raat</TD><TD>Aaj Ki Raat.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD></TD><TD>D:\songs\2008\SONG\songs 2006\02 Don\06 Don The Theme (Instrumental).mp3</TD><TD></TD><TD>06 Don The Theme (Instrumental)</TD><TD>Don The Theme (Instrumental).mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD></TD><TD>D:\songs\2008\SONG\songs 2006\02 Don\07 Don Revisited.mp3</TD><TD></TD><TD>07 Don Revisited</TD><TD>Don Revisited.mp3</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD></TD><TD>D:\songs\2008\SONG\songs 2006\02 Don\08 Main Hoon Don (Fncinternational Mix).mp3</TD><TD></TD><TD>08 Main Hoon Don (Fncinternational Mix)</TD><TD>Main Hoon Don (Fncinternational Mix).mp3</TD></TR></TBODY></TABLE>
a. New Folder Name
b. Source File Location + Name + Extension
c. Existing File Name
d. New File Number
e. New File Name
The code will recognise the new folder to be created (The last on the tree).

I have commented code so that you will understand the logic. The code works on three columns only i.e. COL A, COL B, COL E. So you need to ensure that column E contains the name to be assigned.

VBA Code is as below:

Option Explicit
Public Sub CopyMyFiles()
Dim lLastRow As Long, i As Long
Dim sPath As String, sNewName As String
'Finding the last row
lLastRow = Range("B" & Rows.Count).End(xlUp).Row
'For looping through the whole set
For i = 2 To lLastRow 'Change start row to suit
'Specify at least one NEW folder name or sub will exit
If Range("A2").Value2 = "" Then MsgBox "Specify at least one Folder Name!": Exit Sub
'Assigning the path name until a new path name is found!
'Provide path name as D:\test
'If the folder name exists then it will give error and we'll still continue with it
On Error Resume Next
If Range("A" & i).Value2 <> "" Then MkDir Range("A" & i).Value2 _
: sPath = Range("A" & i).Value2 & "\"
On Error GoTo 0
'Creating a full file name from path & file
sNewName = sPath & Range("E" & i).Value2
'Command to copy File from one location to other
FileCopy Range("B" & i).Value2, sNewName
Next i
MsgBox "File Copying is complete!"
End Sub

 

[Ruddles]

The advantage I saw in DOS + SCRIPT method was it would free up Excel as soon as it wrote BAT and SCRIPT file (which would be almost immediate).

The VBA Shell command does this also. Try this:-

Sub ShellTest()
 
  Shell "cmd.exe /c  dir c:\/s"
  Shell "cmd.exe /c  dir c:\/s"
  Shell "cmd.exe /c  dir c:\/s"
  Shell "cmd.exe /c  dir c:\/s"
  Shell "cmd.exe /c  dir c:\/s"
  
  MsgBox "Done!"
 
End Sub

The MsgBox appears immediately and you can visit the DOS windows to see that they are all still active.

ShellWait works differently, however.

 

[taurean]

Thanks for the explanation, its learning curve for me.

The bat file approach will create one command window (hidden due vbHide) and at the end it will call the script file which will pop the message like VBA does and then delete both files.

However, when it comes copying speed I do not have that deeper understanding as to which route is faster.

In the VBA code, I have tried to use VBA's inherent "FileCopy" method.