Macro to open a user specified xls file

mrpink

New Member
Joined
Sep 26, 2002
Messages
7
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:

Code:
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
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

New Member
Joined
Sep 26, 2002
Messages
7
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
 

Forum statistics

Threads
1,181,684
Messages
5,931,412
Members
436,788
Latest member
Oteez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top