Hi, I am new to VB and cant find how to get my macro to open a file. I know
the location of the file but not the filename, I need the user to pick which
file to open. Basically I need the VB to display Excels Open Dialog Box

Many thanks in advance

Sub test()
Dim F As Variant
Dim Myfolder As String
Myfolder = "C:\Temp"
ChDrive Myfolder
ChDir Myfolder

F = Application.GetOpenFilename("XL Files (*.xl*), *.XL*")
If F = False Then Exit Sub
Workbooks.Open F
End Sub

HTH. Best wishes Harald

"PraxisPete" skrev i melding
...
Hi, I am new to VB and can't find how to get my macro to open a file. I
know
the location of the file but not the filename, I need the user to pick
which
file to open. Basically I need the VB to display Excel's Open Dialog Box

Many thanks in advance

Thanks for that Harald, a further development of that is, if all the files
that could be opened required the same password and were to be open as read
only, can this be coded in?

Thank you again

"Harald Staff" wrote:

Sub test()
Dim F As Variant
Dim Myfolder As String
Myfolder = "C:\Temp"
ChDrive Myfolder
ChDir Myfolder

F = Application.GetOpenFilename("XL Files (*.xl*), *.XL*")
If F = False Then Exit Sub
Workbooks.Open F
End Sub

HTH. Best wishes Harald

"PraxisPete" skrev i melding
...
Hi, I am new to VB and can't find how to get my macro to open a file. I
know
the location of the file but not the filename, I need the user to pick
which
file to open. Basically I need the VB to display Excel's Open Dialog Box

Many thanks in advance

Pete,
Read up on the optional parameters to .Open in the help.

NickHK

"PraxisPete" wrote in message
...
Thanks for that Harald, a further development of that is, if all the files
that could be opened required the same password and were to be open as
read
only, can this be coded in?

Thank you again

"Harald Staff" wrote:

Sub test()
Dim F As Variant
Dim Myfolder As String
Myfolder = "C:\Temp"
ChDrive Myfolder
ChDir Myfolder

F = Application.GetOpenFilename("XL Files (*.xl*), *.XL*")
If F = False Then Exit Sub
Workbooks.Open F
End Sub

HTH. Best wishes Harald

"PraxisPete" skrev i melding
...
Hi, I am new to VB and can't find how to get my macro to open a file.
I
know
the location of the file but not the filename, I need the user to pick
which
file to open. Basically I need the VB to display Excel's Open Dialog
Box

Many thanks in advance

Thanks NickHK,
I've read that and can see that there are parameters for ReadOnly and
Password, but, as I understand it, the Open Method requires the FileName
which I do not have, the GetOpenFilename does not need the Filename, but does
not have parameters for ReadOnly and Password. Its like I need a combination
of both.
Many thanks.

"NickHK" wrote:

Pete,
Read up on the optional parameters to .Open in the help.

NickHK

"PraxisPete" wrote in message
...
Thanks for that Harald, a further development of that is, if all the files
that could be opened required the same password and were to be open as
read
only, can this be coded in?

Thank you again

"Harald Staff" wrote:

Sub test()
Dim F As Variant
Dim Myfolder As String
Myfolder = "C:\Temp"
ChDrive Myfolder
ChDir Myfolder

F = Application.GetOpenFilename("XL Files (*.xl*), *.XL*")
If F = False Then Exit Sub
Workbooks.Open F
End Sub

HTH. Best wishes Harald

"PraxisPete" skrev i melding
...
Hi, I am new to VB and can't find how to get my macro to open a file.
I
know
the location of the file but not the filename, I need the user to pick
which
file to open. Basically I need the VB to display Excel's Open Dialog
Box

Many thanks in advance

No, very wrong.
GetOpenFilename does just what it says, gets the filename for a file to be
opened. It doesn NOT open the file. The filename is F in my code.

This F is passed to the Open method in the last line, which is where you add
the parameters:

Workbooks.Open F, , True, , "Password"

HTH. best wishes Harald

"PraxisPete" skrev i melding
...
Thanks NickHK,
I've read that and can see that there are parameters for ReadOnly and
Password, but, as I understand it, the Open Method requires the FileName
which I do not have, the GetOpenFilename does not need the Filename, but
does
not have parameters for ReadOnly and Password. Its like I need a
combination
of both.
Many thanks.

Thank you all for your help. You will have to forgive me for being a bit
slow on the uptake, I am starter at this, but I do understand now.

"Harald Staff" wrote:

No, very wrong.
GetOpenFilename does just what it says, gets the filename for a file to be
opened. It doesn NOT open the file. The filename is F in my code.

This F is passed to the Open method in the last line, which is where you add
the parameters:

Workbooks.Open F, , True, , "Password"

HTH. best wishes Harald

"PraxisPete" skrev i melding
...
Thanks NickHK,
I've read that and can see that there are parameters for ReadOnly and
Password, but, as I understand it, the Open Method requires the FileName
which I do not have, the GetOpenFilename does not need the Filename, but
does
not have parameters for ReadOnly and Password. Its like I need a
combination
of both.
Many thanks.

No problem. Glad to assist.

Best wishes Harald.

"PraxisPete" skrev i melding
...
Thank you all for your help. You will have to forgive me for being a bit
slow on the uptake, I am starter at this, but I do understand now.