Excel 2003
I'm helping an OP to automate a tiresome task of copying from file to file,
over many, many files.
The problem I see is that the source and destination files have the
exact same name. In fact, that is the criteria by which the destination
file is selected. Since a file has to be open to copy from, and a file has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file in
between.
Now that I've written the above I see another way. Change the name of the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the full
path is known.

Is there a better way? Thanks for your help. Otto

the only way to change the name of the file is to save it with a different
name (activeworkbook.SaveAs ) - so it is not easily discarded. You would
need to rename it again after you close the destination file.

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
I'm helping an OP to automate a tiresome task of copying from file to
file,
over many, many files.
The problem I see is that the source and destination files have the
exact same name. In fact, that is the criteria by which the destination
file is selected. Since a file has to be open to copy from, and a file
has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file in
between.
Now that I've written the above I see another way. Change the name of the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the full
path is known.

Is there a better way? Thanks for your help. Otto

Tom
Ron's idea is good for me. The sequence of tasks here is:
Loop through the files in the NEW folder. For each file:
Check if the file name is in the History folder.
If it isn't, copy the file to the History folder.
If it is, then using Ron's idea, change the name of the source file before
opening it.
Open the source & destination files.
Do the copying.
Save the destination file.
Close both and discard (kill) the source file.
Getting all the code right is what I'm working on now. Do you have an idea
of how to change the name of a closed file? Thanks for your help. Otto
"Tom Ogilvy" wrote in message
...
the only way to change the name of the file is to save it with a different
name (activeworkbook.SaveAs ) - so it is not easily discarded. You would
need to rename it again after you close the destination file.

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
I'm helping an OP to automate a tiresome task of copying from file to
file,
over many, many files.
The problem I see is that the source and destination files have the
exact same name. In fact, that is the criteria by which the destination
file is selected. Since a file has to be open to copy from, and a file
has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file in
between.
Now that I've written the above I see another way. Change the name of
the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the
full
path is known.

Is there a better way? Thanks for your help. Otto

changing the name of the closed file is Ron's idea.

Name statement
Renames a disk file, directory, or folder.

--
Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Tom
Ron's idea is good for me. The sequence of tasks here is:
Loop through the files in the NEW folder. For each file:
Check if the file name is in the History folder.
If it isn't, copy the file to the History folder.
If it is, then using Ron's idea, change the name of the source file before
opening it.
Open the source & destination files.
Do the copying.
Save the destination file.
Close both and discard (kill) the source file.
Getting all the code right is what I'm working on now. Do you have an
idea
of how to change the name of a closed file? Thanks for your help. Otto
"Tom Ogilvy" wrote in message
...
the only way to change the name of the file is to save it with a
different
name (activeworkbook.SaveAs ) - so it is not easily discarded. You
would
need to rename it again after you close the destination file.

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
I'm helping an OP to automate a tiresome task of copying from file to
file,
over many, many files.
The problem I see is that the source and destination files have
the
exact same name. In fact, that is the criteria by which the
destination
file is selected. Since a file has to be open to copy from, and a
file
has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file
in
between.
Now that I've written the above I see another way. Change the name of
the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the
full
path is known.

Is there a better way? Thanks for your help. Otto

Thanks Tom. Otto
"Tom Ogilvy" wrote in message
...
changing the name of the closed file is Ron's idea.

Name statement
Renames a disk file, directory, or folder.

--
Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Tom
Ron's idea is good for me. The sequence of tasks here is:
Loop through the files in the NEW folder. For each file:
Check if the file name is in the History folder.
If it isn't, copy the file to the History folder.
If it is, then using Ron's idea, change the name of the source file
before
opening it.
Open the source & destination files.
Do the copying.
Save the destination file.
Close both and discard (kill) the source file.
Getting all the code right is what I'm working on now. Do you have an
idea
of how to change the name of a closed file? Thanks for your help. Otto
"Tom Ogilvy" wrote in message
...
the only way to change the name of the file is to save it with a
different
name (activeworkbook.SaveAs ) - so it is not easily discarded. You
would
need to rename it again after you close the destination file.

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003
I'm helping an OP to automate a tiresome task of copying from file
to
file,
over many, many files.
The problem I see is that the source and destination files have
the
exact same name. In fact, that is the criteria by which the
destination
file is selected. Since a file has to be open to copy from, and a
file
has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file
in
between.
Now that I've written the above I see another way. Change the name
of
the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the
full
path is known.

Is there a better way? Thanks for your help. Otto

Hi Otto

You can use this rename the file in the New folder before you open it
Nname is the full path to the file in the New folder

Name NewN As Left(NewN, Len(NewN) - 4) & "something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Otto Moehrbach" wrote in message ...
Excel 2003
I'm helping an OP to automate a tiresome task of copying from file to file,
over many, many files.
The problem I see is that the source and destination files have the
exact same name. In fact, that is the criteria by which the destination
file is selected. Since a file has to be open to copy from, and a file has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file in
between.
Now that I've written the above I see another way. Change the name of the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the full
path is known.

Is there a better way? Thanks for your help. Otto

Did you mistype something there? Nname is not in the code you wrote.
Thanks for your help. Otto
"Ron de Bruin" wrote in message
...
Hi Otto

You can use this rename the file in the New folder before you open it
Nname is the full path to the file in the New folder

Name NewN As Left(NewN, Len(NewN) - 4) & "something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Otto Moehrbach" wrote in message
...
Excel 2003
I'm helping an OP to automate a tiresome task of copying from file to
file,
over many, many files.
The problem I see is that the source and destination files have the
exact same name. In fact, that is the criteria by which the destination
file is selected. Since a file has to be open to copy from, and a file
has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file in
between.
Now that I've written the above I see another way. Change the name of
the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the
full
path is known.

Is there a better way? Thanks for your help. Otto

Name oldpathname As newpathname

must be done before you open the file.

Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Did you mistype something there? Nname is not in the code you wrote.
Thanks for your help. Otto
"Ron de Bruin" wrote in message
...
Hi Otto

You can use this rename the file in the New folder before you open it
Nname is the full path to the file in the New folder

Name NewN As Left(NewN, Len(NewN) - 4) & "something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Otto Moehrbach" wrote in message
...
Excel 2003
I'm helping an OP to automate a tiresome task of copying from file to
file,
over many, many files.
The problem I see is that the source and destination files have
the
exact same name. In fact, that is the criteria by which the
destination
file is selected. Since a file has to be open to copy from, and a
file
has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file
in
between.
Now that I've written the above I see another way. Change the name of
the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the
full
path is known.

Is there a better way? Thanks for your help. Otto

Thanks Tom. That's clear. Otto
"Tom Ogilvy" wrote in message
...
Name oldpathname As newpathname

must be done before you open the file.

Regards,
Tom Ogilvy

Otto Moehrbach wrote in message
...
Did you mistype something there? Nname is not in the code you wrote.
Thanks for your help. Otto
"Ron de Bruin" wrote in message
...
Hi Otto

You can use this rename the file in the New folder before you open it
Nname is the full path to the file in the New folder

Name NewN As Left(NewN, Len(NewN) - 4) & "something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Otto Moehrbach" wrote in message
...
Excel 2003
I'm helping an OP to automate a tiresome task of copying from file
to
file,
over many, many files.
The problem I see is that the source and destination files have
the
exact same name. In fact, that is the criteria by which the
destination
file is selected. Since a file has to be open to copy from, and a
file
has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file
in
between.
Now that I've written the above I see another way. Change the name
of
the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the
full
path is known.

Is there a better way? Thanks for your help. Otto

It is a example
I don't see you code so I can't use your code

Dim NewN As String
MyPath2 = "C:\New"

If the file exist in both folders you can use this

NewN = MyPath2 & "\" & FNames
Name NewN As Left(NewN, Len(NewN) - 4) & "something.xls"


FNames is the filename that you can use if use the
Dir function to loop through all your files
for a example see this page
http://www.rondebruin.nl/copy3.htm




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Otto Moehrbach" wrote in message ...
Did you mistype something there? Nname is not in the code you wrote.
Thanks for your help. Otto
"Ron de Bruin" wrote in message
...
Hi Otto

You can use this rename the file in the New folder before you open it
Nname is the full path to the file in the New folder

Name NewN As Left(NewN, Len(NewN) - 4) & "something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Otto Moehrbach" wrote in message
...
Excel 2003
I'm helping an OP to automate a tiresome task of copying from file to
file,
over many, many files.
The problem I see is that the source and destination files have the
exact same name. In fact, that is the criteria by which the destination
file is selected. Since a file has to be open to copy from, and a file
has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file in
between.
Now that I've written the above I see another way. Change the name of
the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the
full
path is known.

Is there a better way? Thanks for your help. Otto

Ron
That site is a gold mine. Thanks. Otto
"Ron de Bruin" wrote in message
...
It is a example
I don't see you code so I can't use your code

Dim NewN As String
MyPath2 = "C:\New"

If the file exist in both folders you can use this

NewN = MyPath2 & "\" & FNames
Name NewN As Left(NewN, Len(NewN) - 4) & "something.xls"


FNames is the filename that you can use if use the
Dir function to loop through all your files
for a example see this page
http://www.rondebruin.nl/copy3.htm




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Otto Moehrbach" wrote in message
...
Did you mistype something there? Nname is not in the code you wrote.
Thanks for your help. Otto
"Ron de Bruin" wrote in message
...
Hi Otto

You can use this rename the file in the New folder before you open it
Nname is the full path to the file in the New folder

Name NewN As Left(NewN, Len(NewN) - 4) & "something.xls"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Otto Moehrbach" wrote in message
...
Excel 2003
I'm helping an OP to automate a tiresome task of copying from file
to
file,
over many, many files.
The problem I see is that the source and destination files have
the
exact same name. In fact, that is the criteria by which the
destination
file is selected. Since a file has to be open to copy from, and a
file
has
to be open to paste to, how to get from here to there?
The only solution I can see at the moment is to use a dummy file
in
between.
Now that I've written the above I see another way. Change the name
of
the
source file (it will be discarded after the copy/paste).
Given that, what is the code to change the name of a file, given the
full
path is known.

Is there a better way? Thanks for your help. Otto

Hmmm. I would read the New folder first, check if that name exists in history, if not, move it over there, if so, copy the rows from the new folder workbook you need, close that workbook, keep the contents of the clipboard, open the one in history, paste it and so on. Seems like that would work.

Slark
I'll give that a shot. Excel has such a propensity to clear the
clipboard that I just dismissed that idea. But maybe it will work. Thanks.
Otto
"slark" wrote in message
...
Hmmm. I would read the New folder first, check if that name exists in
history, if not, move it over there, if so, copy the rows from the new
folder workbook you need, close that workbook, keep the contents of the
clipboard, open the one in history, paste it and so on. Seems like that
would work.