Locate Workbook

K

kev

Hi to all you nice GURU's who have helped me SO much. First of all, Thank You.
Now to my question;
I have a workbook that stores "data" into another workbook, then hides the
"data" workbook. All works well with this macro;

Sub testmove1()
'
' testmove1 Macro
'
'
Range("I5:I7").Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Documents and Settings\Kev\Desktop\HyVee\Project\BOtestdata.xls"
Windows("BOtestdata.xls").Activate
Range("K1").Select
ActiveSheet.Paste
ActiveWindow.Visible = False
Range("E12").Select
End Sub
My problem is I'm building this at home for the office. When I transfer this
to my office, how to I "without having to "remake the macro", find the data
workbook.
I have it in the same dir as the source but the only way I can get it to
work is as in the code above.
Thank You
 
B

Bob Phillips

Use GetOpenFilename

Sub testmove1()
Dim sFilename As String
Dim oWBTarget As Workbook
Range("I5:I7").Copy
sFilename = Application _
.GetOpenFilename("Microsoft Excel Files (*.xls), *.xls")
If sFilename <> False Then
Set oWBTarget = Workbooks.Open(Filename:=sFilename)
oWBTarget.Activate
Range("K1").Select
ActiveSheet.Paste
ActiveWindow.Visible = False
Range("E12").Select
End If
End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
Top