Is it possible to run DOS commands in Excel?

[hce]

Dear All

I would like to check whether is it possible to run DOS commands i
Excel...?

Currently, I have to copy many files every month using DOS. For eg. DO
command = copy 123aug.txt 123sep.txt

In a nutshell, I would need to make a copy of a specific file and the
rename it as the current month so that I will have the file for ever
month...

I thought of writing a code to open the file and then do a "save as"..
But does anyone know how I can do the same thing but without opening th
file... because there are different files (text) and their delimiter
are different...

Hope you guys understand my problem... I will really appreciate an
suggestions..

Cheer

 

[Ivan F Moala]

Hi hce

Look up help for [Name Statement]

From help

Name Statement Example
This example uses the Name statement to rename a file. For purposes o
this example, assume that the directories or folders that are specifie
already exist. On the Macintosh, “HD:” is the default drive name an
portions of the pathname are separated by colons instead o
backslashes.

Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file

 

[Rob van Gelder]

It *IS* possible to run commands.
Shell is the VBA command you're after.

Keep in mind that it runs asynchronously. So it wont wait for the command to
finish.

If this is an issue for you, do a google search for: ShellWait VB

 

[Rob van Gelder]

I thought OP was after Copy, not Rename.

 

[Ivan F Moala]

Ah, yes you are correct

You can use the FileSystemObject

No need to shell out to Dos

'// You can either
'// 1) Set a reference to the [Microsoft Scripting Runtime]
FileSystemObject.CopyFile "c:\mydocuments\My.txt"
"c:\mydocuments\MyNew.txt"

'// OR 2) create the reference via Late Binding
Dim Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
Fso.CopyFile "c:\mydocuments\My.txt", "c:\mydocuments\MyNew.txt"


'// OR 3) Use just use Native VBA to copy
FileCopy "c:\mydocuments\My.txt", "c:\mydocuments\MyNew.txt"

If you really need to Shell out to Dos using core Dos commands such a
Copy then you need to use the c switch together
with the command.com eg
Shell "command.com /c MD C:\Amydir C:\Amydir", vbNormalFocus

If running it asynchronously is a problem then use the Window
Scripting Host object model.

A method exposed by this library lets you to run a file,
and specify whether you want to wait for its termination.
To use the following code snippet you must add a reference
to Windows Scripting Host Object model in the "References " dialo
window.
The method you're looking for is the Run method of the IWshShell_Clas
object.
Its first argument is the file to run, the second specifies the style
of the
application's window and the last is a Boolean value that tells whethe
you
want to wait for the program termination. Here's a function tha
contains all
the necessary code:

Sub RunAndWait(ByVal sFile As String)
Dim WSHShell As New IWshShell_Class
WSHShell.Run sFile, , True
End Sub

'To test this function, write the following code in your form
'(it assumes that Notepad is in your system path):

Sub Tester()
RunAndWait "notepad.exe"
MsgBox "Back to your application!"
End Sub

I thought OP was after Copy, not Rename.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


message

Hi hce

Look up help for [Name Statement]

From help

Name Statement Example
This example uses the Name statement to rename a file. For purpose of
this example, assume that the directories or folders that ar specified
already exist. On the Macintosh, “HD:” is the default drive name and
portions of the pathname are separated by colons instead of
backslashes.

Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.

 

[Myrna Larson]

VBA also has a FileCopy statement. No need to use the File System Object.

Ah, yes you are correct

You can use the FileSystemObject

No need to shell out to Dos

'// You can either
'// 1) Set a reference to the [Microsoft Scripting Runtime]
FileSystemObject.CopyFile "c:\mydocuments\My.txt",
"c:\mydocuments\MyNew.txt"

'// OR 2) create the reference via Late Binding
Dim Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
Fso.CopyFile "c:\mydocuments\My.txt", "c:\mydocuments\MyNew.txt"


'// OR 3) Use just use Native VBA to copy
FileCopy "c:\mydocuments\My.txt", "c:\mydocuments\MyNew.txt"

If you really need to Shell out to Dos using core Dos commands such as
Copy then you need to use the c switch together
with the command.com eg
Shell "command.com /c MD C:\Amydir C:\Amydir", vbNormalFocus

If running it asynchronously is a problem then use the Windows
Scripting Host object model.

A method exposed by this library lets you to run a file,
and specify whether you want to wait for its termination.
To use the following code snippet you must add a reference
to Windows Scripting Host Object model in the "References " dialog
window.
The method you're looking for is the Run method of the IWshShell_Class
object.
Its first argument is the file to run, the second specifies the styles
of the
application's window and the last is a Boolean value that tells whether
you
want to wait for the program termination. Here's a function that
contains all
the necessary code:

Sub RunAndWait(ByVal sFile As String)
Dim WSHShell As New IWshShell_Class
WSHShell.Run sFile, , True
End Sub

'To test this function, write the following code in your form
'(it assumes that Notepad is in your system path):

Sub Tester()
RunAndWait "notepad.exe"
MsgBox "Back to your application!"
End Sub

I thought OP was after Copy, not Rename.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


message

Hi hce

Look up help for [Name Statement]

From help

Name Statement Example
This example uses the Name statement to rename a file. For purposes of
this example, assume that the directories or folders that are specified
already exist. On the Macintosh, “HD:” is the default drive name and
portions of the pathname are separated by colons instead of
backslashes.

Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.

 

[Ivan F Moala]

Yes, I know see No3 in my post

VBA also has a FileCopy statement. No need to use the File Syste
Object.

Ah, yes you are correct

You can use the FileSystemObject

No need to shell out to Dos

'// You can either
'// 1) Set a reference to the [Microsoft Scripting Runtime]
FileSystemObject.CopyFile "c:\mydocuments\My.txt",
"c:\mydocuments\MyNew.txt"

'// OR 2) create the reference via Late Binding
Dim Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
Fso.CopyFile "c:\mydocuments\My.txt", "c:\mydocuments\MyNew.txt"


'// OR 3) Use just use Native VBA to copy
FileCopy "c:\mydocuments\My.txt", "c:\mydocuments\MyNew.txt"

If you really need to Shell out to Dos using core Dos commands suc as
Copy then you need to use the c switch together
with the command.com eg
Shell "command.com /c MD C:\Amydir C:\Amydir", vbNormalFocus

If running it asynchronously is a problem then use the Windows
Scripting Host object model.

A method exposed by this library lets you to run a file,
and specify whether you want to wait for its termination.
To use the following code snippet you must add a reference
to Windows Scripting Host Object model in the "References " dialog
window.
The method you're looking for is the Run method of th IWshShell_Class
object.
Its first argument is the file to run, the second specifies th styles
of the
application's window and the last is a Boolean value that tell whether
you
want to wait for the program termination. Here's a function that
contains all
the necessary code:

Sub RunAndWait(ByVal sFile As String)
Dim WSHShell As New IWshShell_Class
WSHShell.Run sFile, , True
End Sub

'To test this function, write the following code in your form
'(it assumes that Notepad is in your system path):

Sub Tester()
RunAndWait "notepad.exe"
MsgBox "Back to your application!"
End Sub

I thought OP was after Copy, not Rename.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


message

Hi hce

Look up help for [Name Statement]

From help

Name Statement Example
This example uses the Name statement to rename a file. Fo purposes
of
this example, assume that the directories or folders that are
specified
already exist. On the Macintosh, “HD:” is the default drive nam and
portions of the pathname are separated by colons instead of
backslashes.

Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.

 

[Myrna Larson]

Hi, Ivan:

My comment was intended for the OP. I don't think that was you, was it?

Yes, I know see No3 in my post

VBA also has a FileCopy statement. No need to use the File System
Object.

Ah, yes you are correct

You can use the FileSystemObject

No need to shell out to Dos

'// You can either
'// 1) Set a reference to the [Microsoft Scripting Runtime]
FileSystemObject.CopyFile "c:\mydocuments\My.txt",
"c:\mydocuments\MyNew.txt"

'// OR 2) create the reference via Late Binding
Dim Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
Fso.CopyFile "c:\mydocuments\My.txt", "c:\mydocuments\MyNew.txt"


'// OR 3) Use just use Native VBA to copy
FileCopy "c:\mydocuments\My.txt", "c:\mydocuments\MyNew.txt"

If you really need to Shell out to Dos using core Dos commands such as
Copy then you need to use the c switch together
with the command.com eg
Shell "command.com /c MD C:\Amydir C:\Amydir", vbNormalFocus

If running it asynchronously is a problem then use the Windows
Scripting Host object model.

A method exposed by this library lets you to run a file,
and specify whether you want to wait for its termination.
To use the following code snippet you must add a reference
to Windows Scripting Host Object model in the "References " dialog
window.
The method you're looking for is the Run method of the IWshShell_Class
object.
Its first argument is the file to run, the second specifies the styles
of the
application's window and the last is a Boolean value that tells whether
you
want to wait for the program termination. Here's a function that
contains all
the necessary code:

Sub RunAndWait(ByVal sFile As String)
Dim WSHShell As New IWshShell_Class
WSHShell.Run sFile, , True
End Sub

'To test this function, write the following code in your form
'(it assumes that Notepad is in your system path):

Sub Tester()
RunAndWait "notepad.exe"
MsgBox "Back to your application!"
End Sub



Rob van Gelder Wrote:
I thought OP was after Copy, not Rename.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


message

Hi hce

Look up help for [Name Statement]

From help

Name Statement Example
This example uses the Name statement to rename a file. For purposes
of
this example, assume that the directories or folders that are
specified
already exist. On the Macintosh, “HD:” is the default drive name and
portions of the pathname are separated by colons instead of
backslashes.

Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define file names.
Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.

 

[Ivan F Moala]

I took it you meant me as you quoted my post ??

Hi, Ivan:

My comment was intended for the OP. I don't think that was you, wa
it?

Yes, I know see No3 in my post

VBA also has a FileCopy statement. No need to use the File System
Object.


On Tue, 12 Oct 2004 04:27:24 -0500, Ivan F Moala


Ah, yes you are correct

You can use the FileSystemObject

No need to shell out to Dos

'// You can either
'// 1) Set a reference to the [Microsoft Scripting Runtime]
FileSystemObject.CopyFile "c:\mydocuments\My.txt",
"c:\mydocuments\MyNew.txt"

'// OR 2) create the reference via Late Binding
Dim Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
Fso.CopyFile "c:\mydocuments\My.txt", "c:\mydocuments\MyNew.txt"


'// OR 3) Use just use Native VBA to copy
FileCopy "c:\mydocuments\My.txt", "c:\mydocuments\MyNew.txt"

If you really need to Shell out to Dos using core Dos command such
as
Copy then you need to use the c switch together
with the command.com eg
Shell "command.com /c MD C:\Amydir C:\Amydir", vbNormalFocus

If running it asynchronously is a problem then use the Windows
Scripting Host object model.

A method exposed by this library lets you to run a file,
and specify whether you want to wait for its termination.
To use the following code snippet you must add a reference
to Windows Scripting Host Object model in the "References " dialog
window.
The method you're looking for is the Run method of the
IWshShell_Class
object.
Its first argument is the file to run, the second specifies the
styles
of the
application's window and the last is a Boolean value that tells
whether
you
want to wait for the program termination. Here's a function that
contains all
the necessary code:

Sub RunAndWait(ByVal sFile As String)
Dim WSHShell As New IWshShell_Class
WSHShell.Run sFile, , True
End Sub

'To test this function, write the following code in your form
'(it assumes that Notepad is in your system path):

Sub Tester()
RunAndWait "notepad.exe"
MsgBox "Back to your application!"
End Sub



Rob van Gelder Wrote:
I thought OP was after Copy, not Rename.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Ivan F Moala" <[email protected]> wrot in
message

Hi hce

Look up help for [Name Statement]

From help

Name Statement Example
This example uses the Name statement to rename a file. For
purposes
of
this example, assume that the directories or folders that are
specified
already exist. On the Macintosh, “HD:” is the default driv name
and
portions of the pathname are separated by colons instead of
backslashes.

Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE" ' Define fil names.
Name OldName As NewName ' Rename file.

OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
Name OldName As NewName ' Move and rename file.