I currently have a model which really consists of two
workbooks. One workbook is the data input workbook in
which all the data is entered by one person. The second
workbook is used by another person and needs to be able to
import information from the first workbook with a macro.
However, a simple link will not work as I want a macro
that will allow the user to define which workbook to
import the information from as this changes all of the
time. Please help?

I used code similar to this for situations similar to yours. I am sur
there may be easier ways to accomplish this task, but I found these tw
options worked for me. This first macro simply opens the OpenFil
dialog box, you select the file you want, and then it opens the fil
you selected. If you hit Cancel in the dialog box, the macro jus
terminates.

Sub Open_File()
Dim strFile As String
strFile = Application.GetOpenFilename("Excel files (*.xl*),*.xl*")
If strFile = False Then Exit Sub
Workbooks.Open FileName:=strFile
End Sub

This second macro allows you to set a standard path. If you keep you
data files in the same folder all the time, it will probably make i
easier for you to just type in the file name. The .xls extension doe
not have to be typed in the InputBox, but it won't hurt if it is.
simple error handler gives the user a message and exits the macro i
the file name typed in does not exist. If you wish, you can separat
out the InputBox command, assign to another variable, and add whateve
other error handling you want to trap.

Sub Open_File2()
Dim strPath As String
On Error GoTo No_File
strPath = "C:\YourPathTo\Data\"
Workbooks.Open FileName:=strPath & InputBox("File Name")
Exit Sub
No_File:
MsgBox "That file does not exits."
End Sub


Just some of the ways I have handled your situation. Let me know i
this helps!


JerryG :

--
Message posted from http://www.ExcelForum.com

i tried this function. when i typed filename i got files does not exis
am i supposed to change the path. in the editor.??

--
Message posted from http://www.ExcelForum.com

Yes, if you are using the second macro, you must replace
C:\YourPathTo\Data\ with your correct path in the VB editor.


---
Message posted from http://www.ExcelForum.com/

i used the fisrt macro jerry posted and ran it.. it popped up the open
box and selected my file and got Run-time error 13/ type mismatch


---
Message posted from http://www.ExcelForum.com/

Sorry, missed some quotes. First macro should be . . .

Sub Open_File()
Dim strFile As String
strFile = Application.GetOpenFilename("Excel files (*.xl*),*.xl*")
If strFile = "False" Then Exit Sub
Workbooks.Open FileName:=strFile
End Sub


JerryG ;)


---
Message posted from http://www.ExcelForum.com/