Macro to open a user specified xls file

[mrpink]

Hi y'all,

As I am still a total vba newbie, I need some of your expert help ...
I need a macro that gives the user a dialog-box where he/she can specify an xls file to be opened, unless it is already open. In that case I need a message saying that the file is already in use.
this is what I already managed to do:

Sub Inputfile()

 Dim path As String

 path = InputBox("Enter the location of the file in this Input Box :", _
  , "enter file location here")

 If path<> "" Then MsgBox path

 Workbooks.Open Filename:=path
  
End Sub

There is still something wrong with the line Workbooks.Open Filename:=path because after running the macro, the editor makes this line yellow...
Dunno how to fix it

one more thing, anybody knows a good tutorial how to make a shortcut button for this/a macro
This message was edited by mrpink on 2002-10-02 09:25

 

[Barrie Davidson]

Try this:

<PRE>
Sub Inputfile()
'Written by Barrie Davidson

Dim path As Variant
Dim i As Long

On Error GoTo ErrorHandler

path = Application.GetOpenFilename("Microsoft Excel (*.xls),*.xl*")
For i = 1 To Application.Workbooks.Count
If Workbooks(i).FullName = path Then
MsgBox ("File already open")
Exit Sub
End If
Next i


Workbooks.Open FileName:=path

Exit Sub

ErrorHandler:
MsgBox ("Error Encountered")

End Sub

</PRE>

Regards,

 

[mrpink]

Thanks for your fast reply, it works like a charm

I got another question: if changes are made to the (newly) "macro-"opened file, it needs to be saved automatically when the file is closed. Ie, it needs to be saved without the save-as dialog box opening, it just needs to overwrite the existing file and then close itself.
Now how can I this?
This message was edited by mrpink on 2002-10-02 10:10