Open a file
 

Hello

I would like to copy and paste info from one workbook to another.
However i want to be able to choose the file from where i am going to copy
the values.
the problem is that the file could be in any subdirectory (it's not one file
there are many files
and i want to be able to choose its time which one i am going to use). If i
achieve to have both files open
(the one that i will copy the values and the one i will paste(this is
constant)) then i know the code
But i want you to help me how to open any file i want to copy values

Sub Macro4()
ChDir "E:\Folder1\Folder 2\Workbook1.xls"
Workbooks.Open Filename:= _
"E:\Folder1\Folder 2\Workbook1.xls"
Windows("New Model_1j.xls").Activate
Range("A1").Select
End Sub

Workbook1 (can be in any subdirectory but it has the same name ) = copy
values
New Model (same every time) = paste values

I record a macro how to open a file but i have to write a secific file

Is it possible t choose each time what to use?

Thanks in advance

Angeliki

Open a file
 

read HELP on Application.GetOpenFilename

The following frame will help to get you started.

Sub GetFiles()
Dim sCopyFrom As String, wbFrom As Workbook
Dim sCopyTo As String, wbTo As Workbook
sCopyFrom = Application.GetOpenFilename()
sCopyTo = Application.GetOpenFilename()

Set wbFrom = Workbooks.Open(sCopyFrom)
Set wbTo = Workbooks.Open(sCopyTo)

' copy some data


wbFrom.Close False
wbTo.Close True ' true = Save

Set wbFrom = Nothing
Set wbTo = Nothing

End Sub

befor GetOpenFileName method is called, you may set the
drive & target folder as per your code using ChDrive and
ChDir methods.

The notes in Help will show you how to set the filter for
just EXcel Files...
sCopyFrom = _
Application.GetOpenFilename("Excel Workbooks _
(*.xls),*.xls")

Patrick Molloy
Microsoft Excel MVP


-----Original Message-----
Hello

I would like to copy and paste info from one workbook to
another.
However i want to be able to choose the file from where
i am going to copy
the values.
the problem is that the file could be in any
subdirectory (it's not one file
there are many files
and i want to be able to choose its time which one i am
going to use). If i
achieve to have both files open
(the one that i will copy the values and the one i will
paste(this is
constant)) then i know the code
But i want you to help me how to open any file i want to
copy values

Sub Macro4()
ChDir "E:\Folder1\Folder 2\Workbook1.xls"
Workbooks.Open Filename:= _
"E:\Folder1\Folder 2\Workbook1.xls"
Windows("New Model_1j.xls").Activate
Range("A1").Select
End Sub

Workbook1 (can be in any subdirectory but it has the
same name ) = copy
values
New Model (same every time) = paste values

I record a macro how to open a file but i have to write
a secific file

Is it possible t choose each time what to use?

Thanks in advance

Angeliki


.