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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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,144,741
Messages
5,726,010
Members
422,652
Latest member
Elnene1

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