h
For some reason, i have to write a macro to copy some ranges of two files with the same name but in different directories.
then paste them to target file. The process is quite simple but in Excel, open two files with the same name even from different directories is not allowed. Therefore, i figured out a compromised way to do is: open file1, copy and paste i
to target file; close file1. Then ask user to open file2; then press a command button to do the thing of copy an
paste it to the third file. Of course, it works fine but I still wanna know is there any better solution to do with this problem

An ideal solution for me is user open two files concurrently then press a button that copy and paste to the target file
Thank you very much

How about using VBA's FileCopy to make a copy of the second workbook. Name it
something that won't cause a problem.

Open that, copy, paste, close, and kill that copied file.



joanne wrote:

hi
For some reason, i have to write a macro to copy some ranges of two files with the same name but in different directories.
then paste them to target file. The process is quite simple but in Excel, open two files with the same name even from different directories is not allowed. Therefore, i figured out a compromised way to do is: open file1, copy and paste it
to target file; close file1. Then ask user to open file2; then press a command button to do the thing of copy and
paste it to the third file. Of course, it works fine but I still wanna know is there any better solution to do with this problem?

An ideal solution for me is user open two files concurrently then press a button that copy and paste to the target file.
Thank you very much.

--

Dave Peterson

You could always use GetOpenFilename method to direct the user to open
the file(s) at the appropriate positions. Something like:

Get name with GetOpenFilename, open file, process it, close it
Get next name with GetOpenFilename, open file, process it, close it.

You could even generalize it to any number of files by embedding the
result of GetOpenFilename in a loop. Something like the untested

Option Explicit
Option Compare Text

Sub testIt()
Dim aName As Variant
Do
aName = GetOpenFilename()
If aName = False Then
Else
'do your thing
End If
Loop While TypeName(aName) < "boolean"
End Sub

For more check XL VBA help for GetOpenFilename.



--
Regards,

Tushar Mehta
www.tushar-mehta.com
Business solutions leveraging technology
Microsoft Most Valuable Professional (MVP) 2000-2004

In article ,
says...
hi
For some reason, i have to write a macro to copy some ranges of two files with the same name but in different directories.
then paste them to target file. The process is quite simple but in Excel, open two files with the same name even from different directories is not allowed. Therefore, i figured out a compromised way to do is: open file1, copy and paste it
to target file; close file1. Then ask user to open file2; then press a command button to do the thing of copy and
paste it to the third file. Of course, it works fine but I still wanna know is there any better solution to do with this problem?

An ideal solution for me is user open two files concurrently then press a button that copy and paste to the target file.
Thank you very much.