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

--
hc
-----------------------------------------------------------------------
hce's Profile: http://www.excelforum.com/member.php...nfo&userid=351
View this thread: http://www.excelforum.com/showthread.php?threadid=26838

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

--
Ivan F Moal

-----------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...nfo&userid=195
View this thread: http://www.excelforum.com/showthread.php?threadid=26838

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 - http://www.vangelder.co.nz/excel


"hce" wrote in message
...

Dear All

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

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

In a nutshell, I would need to make a copy of a specific file and then
rename it as the current month so that I will have the file for every
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 the
file... because there are different files (text) and their delimiters
are different...

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

Cheers


--
hce
------------------------------------------------------------------------
hce's Profile:
http://www.excelforum.com/member.php...fo&userid=3518
View this thread: http://www.excelforum.com/showthread...hreadid=268387

I thought OP was after Copy, not Rename.

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


"Ivan F Moala" wrote 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 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


------------------------------------------------------------------------
Ivan F Moala's Profile:
http://www.excelforum.com/member.php...fo&userid=1954
View this thread: http://www.excelforum.com/showthread...hreadid=268387

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



Rob van Gelder Wrote:[color=blue]
I thought OP was after Copy, not Rename.

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


"Ivan F Moala" wrote i
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.


--
Ivan F Moal

-----------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...nfo&userid=195
View this thread: http://www.excelforum.com/showthread.php?threadid=26838

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
wrote:
[color=blue]

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


"Ivan F Moala" wrote 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 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.

Yes, :) I know see No3 in my post

Myrna Larson Wrote:[color=blue]
VBA also has a FileCopy statement. No need to use the File Syste
Object.


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


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



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

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


"Ivan F Moala" wrote 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. 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.


--
Ivan F Moal

-----------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...nfo&userid=195
View this thread: http://www.excelforum.com/showthread.php?threadid=26838

Hi, Ivan:

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

On Tue, 12 Oct 2004 21:19:02 -0500, Ivan F Moala
wrote:
[color=blue]

Yes, :) I know see No3 in my post

Myrna Larson Wrote:
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
wrote:


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


"Ivan F Moala" wrote 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 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.

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

Myrna Larson Wrote:[color=blue]
Hi, Ivan:

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



On Tue, 12 Oct 2004 21:19:02 -0500, Ivan F Moala
wrote:


Yes, :) I know see No3 in my post

Myrna Larson Wrote:
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
wrote:


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" 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.


--
Ivan F Moal

-----------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...nfo&userid=195
View this thread: http://www.excelforum.com/showthread...hreadid=268387