GetOpenFilename

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

Not sure if this would apply to your code in particular, but have you thought of using:

Thisworkbook.Worksheets("sheet1").range("A1")

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("A1")

--
Regards

Juan Pablo González

"Roman" <[email protected]> wrote in message
news:[email protected]...
> 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