Here's my example:

Book = Application.GetOpenFilename
Set Rng = Workbooks(Book).Worksheet("Sheet1").Range("A1")

It results in error because GetOpenFilename returns a full file path, not
file name.
Is there a way to solve this?

Thanks

GetOpenFileName returns a string, but does not open the chosen book, you
have to do it yourself:

Dim f As String
Dim wkb As Workbook
Dim Rg As Range

f = Application.GetOpenFilename()
If f = "False" Then
MsgBox "Cancelled by user"
Exit Sub
End If

Set wkb = Workbooks.Open(f)
Set Rg = wkb.Worksheet("Sheet1").Range("A1")

Regards,
Sebastien

"Roman" wrote:

Here's my example:

Book = Application.GetOpenFilename
Set Rng = Workbooks(Book).Worksheet("Sheet1").Range("A1")

It results in error because GetOpenFilename returns a full file path, not
file name.
Is there a way to solve this?

Thanks

one more thing: to prevent user from choosing non-excel files, you could use
f = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
This will only show xls files.
Sebastien

"sebastienm" wrote:

GetOpenFileName returns a string, but does not open the chosen book, you
have to do it yourself:

Dim f As String
Dim wkb As Workbook
Dim Rg As Range

f = Application.GetOpenFilename()
If f = "False" Then
MsgBox "Cancelled by user"
Exit Sub
End If

Set wkb = Workbooks.Open(f)
Set Rg = wkb.Worksheet("Sheet1").Range("A1")

Regards,
Sebastien

"Roman" wrote:

Here's my example:

Book = Application.GetOpenFilename
Set Rng = Workbooks(Book).Worksheet("Sheet1").Range("A1")

It results in error because GetOpenFilename returns a full file path, not
file name.
Is there a way to solve this?

Thanks

Until the user types:
*.*
in the filename box in that dialog.



sebastienm wrote:

one more thing: to prevent user from choosing non-excel files, you could use
f = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
This will only show xls files.
Sebastien

"sebastienm" wrote:

GetOpenFileName returns a string, but does not open the chosen book, you
have to do it yourself:

Dim f As String
Dim wkb As Workbook
Dim Rg As Range

f = Application.GetOpenFilename()
If f = "False" Then
MsgBox "Cancelled by user"
Exit Sub
End If

Set wkb = Workbooks.Open(f)
Set Rg = wkb.Worksheet("Sheet1").Range("A1")

Regards,
Sebastien

"Roman" wrote:

Here's my example:

Book = Application.GetOpenFilename
Set Rng = Workbooks(Book).Worksheet("Sheet1").Range("A1")

It results in error because GetOpenFilename returns a full file path, not
file name.
Is there a way to solve this?

Thanks

--

Dave Peterson

very true, Dave.
Sebastien

Application.GetOpenFilename doesn't open the file, it just returns the
filename that the user chose OR False, if the user canceled.

Is the book open when you try to assign the range ? if so, then you don't
need GetOpenFileName, if its not, then try

Set Rng = Workbooks.Open(Book).Worksheets("Sheet1").Range("A 1")

--
Regards

Juan Pablo González

"Roman" wrote in message
...
Here's my example:

Book = Application.GetOpenFilename
Set Rng = Workbooks(Book).Worksheet("Sheet1").Range("A1")

It results in error because GetOpenFilename returns a full file path, not
file name.
Is there a way to solve this?

Thanks