Open up a WB 
Author Message
 Open up a WB

Hi again
I'm trying to open a workbook from within another WB.  At
present, I have:

Dim WB As Workbook
Set WB = Workbooks.Open("E:\Horizon\Quote Sheets\Material
and Labour Pricing.xls")

and it works great, as long as the filename is the same.  
But what if the filename changes?  Then I've tried this:

dlganswer = Application.Dialogs(xlDialogOpen).Show

which also works great as it allows me to select the WB.  
The only problem is that I have to switch between the WBs
to copy and paste, and then close the other one.  I have a
statement to go back to the other WB:

Windows("Materials and Labour Pricing.xls").Activate

This works as long as the filename is the same.  I would
like it to read something like:

Windows(WB).Activate

which would solve my problem if I could get the initial
statements to work.  I've tried merging the two statements:

Dim WB As Workbook
Set WB = Application.Dialogs(xlDialogOpen).Show

but all I get is "Compile error, type mismatch".  I'm not
understanding the error or what is needed.  I've tried
several different things with no success.  I'm hoping that
someone is able to steer me in the right direction.  
Thanks.

Art



Thu, 24 Nov 2005 20:25:20 GMT  
 Open up a WB
Try this

Sub test()
    Dim fname As Variant
    Dim wb As Workbook
    fname = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
    If fname <> False Then
        Set wb = Workbooks.Open(fname)
    End If
    MsgBox "your code"
    wb.Close
End Sub

you can use WB now instead of the workbook name to do your things
In this example it will close the file after the MsgBox

--
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl

Quote:

> Hi again
> I'm trying to open a workbook from within another WB.  At
> present, I have:

> Dim WB As Workbook
> Set WB = Workbooks.Open("E:\Horizon\Quote Sheets\Material
> and Labour Pricing.xls")

> and it works great, as long as the filename is the same.
> But what if the filename changes?  Then I've tried this:

> dlganswer = Application.Dialogs(xlDialogOpen).Show

> which also works great as it allows me to select the WB.
> The only problem is that I have to switch between the WBs
> to copy and paste, and then close the other one.  I have a
> statement to go back to the other WB:

> Windows("Materials and Labour Pricing.xls").Activate

> This works as long as the filename is the same.  I would
> like it to read something like:

> Windows(WB).Activate

> which would solve my problem if I could get the initial
> statements to work.  I've tried merging the two statements:

> Dim WB As Workbook
> Set WB = Application.Dialogs(xlDialogOpen).Show

> but all I get is "Compile error, type mismatch".  I'm not
> understanding the error or what is needed.  I've tried
> several different things with no success.  I'm hoping that
> someone is able to steer me in the right direction.
> Thanks.

> Art



Thu, 24 Nov 2005 21:19:44 GMT  
 Open up a WB
You can let a user pick a filename using application.getopenfilename (see help
for all the options).

Then once you get the name, you can set your workbook to that file.  Then in
further code, you refer to the workbook using your workbook variable--not the
name of the file.

Option Explicit
Sub testme01()

    Dim myFileName As Variant
    Dim wb As Workbook

    myFileName = Application.GetOpenFilename("Excel Files, *.xls")
    If myFileName = False Then
        Exit Sub  'user hit cancel
    End If

    Set wb = Workbooks.Open(Filename:=myFileName)

    wb.Worksheets(1).Range("a1").Value = "No name required!"

    wb.Close savechanges:=True  'false

End Sub

wb.activate would work, too.  But you don't usually have to select anything to
work with it.  YOu can assign a value directly to a range (like I did) or
retrieve data or sort or ....

And there's an Application.GetSaveAsFilename that does the similar thing when
you want to save a file.

Quote:

> Hi again
> I'm trying to open a workbook from within another WB.  At
> present, I have:

> Dim WB As Workbook
> Set WB = Workbooks.Open("E:\Horizon\Quote Sheets\Material
> and Labour Pricing.xls")

> and it works great, as long as the filename is the same.
> But what if the filename changes?  Then I've tried this:

> dlganswer = Application.Dialogs(xlDialogOpen).Show

> which also works great as it allows me to select the WB.
> The only problem is that I have to switch between the WBs
> to copy and paste, and then close the other one.  I have a
> statement to go back to the other WB:

> Windows("Materials and Labour Pricing.xls").Activate

> This works as long as the filename is the same.  I would
> like it to read something like:

> Windows(WB).Activate

> which would solve my problem if I could get the initial
> statements to work.  I've tried merging the two statements:

> Dim WB As Workbook
> Set WB = Application.Dialogs(xlDialogOpen).Show

> but all I get is "Compile error, type mismatch".  I'm not
> understanding the error or what is needed.  I've tried
> several different things with no success.  I'm hoping that
> someone is able to steer me in the right direction.
> Thanks.

> Art

--

Dave Peterson



Thu, 24 Nov 2005 21:23:44 GMT  
 Open up a WB
Oops..

The End If must be at the end

Sub test()
    Dim fname As Variant
    Dim wb As Workbook
    fname = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
    If fname <> False Then
        Set wb = Workbooks.Open(fname)
    MsgBox "your code"
    wb.Close
End If
End Sub

--
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl


Quote:
> Try this

> Sub test()
>     Dim fname As Variant
>     Dim wb As Workbook
>     fname = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls")
>     If fname <> False Then
>         Set wb = Workbooks.Open(fname)
>     End If
>     MsgBox "your code"
>     wb.Close
> End Sub

> you can use WB now instead of the workbook name to do your things
> In this example it will close the file after the MsgBox

> --
> Regards Ron de Bruin
> (Win XP Pro SP-1  XL2002 SP-2)
> www.rondebruin.nl


> > Hi again
> > I'm trying to open a workbook from within another WB.  At
> > present, I have:

> > Dim WB As Workbook
> > Set WB = Workbooks.Open("E:\Horizon\Quote Sheets\Material
> > and Labour Pricing.xls")

> > and it works great, as long as the filename is the same.
> > But what if the filename changes?  Then I've tried this:

> > dlganswer = Application.Dialogs(xlDialogOpen).Show

> > which also works great as it allows me to select the WB.
> > The only problem is that I have to switch between the WBs
> > to copy and paste, and then close the other one.  I have a
> > statement to go back to the other WB:

> > Windows("Materials and Labour Pricing.xls").Activate

> > This works as long as the filename is the same.  I would
> > like it to read something like:

> > Windows(WB).Activate

> > which would solve my problem if I could get the initial
> > statements to work.  I've tried merging the two statements:

> > Dim WB As Workbook
> > Set WB = Application.Dialogs(xlDialogOpen).Show

> > but all I get is "Compile error, type mismatch".  I'm not
> > understanding the error or what is needed.  I've tried
> > several different things with no success.  I'm hoping that
> > someone is able to steer me in the right direction.
> > Thanks.

> > Art



Thu, 24 Nov 2005 21:24:10 GMT  
 Open up a WB
Thanks.  Works great.  I caught the End If.

Art

Quote:
>-----Original Message-----
>Try this

>Sub test()
>    Dim fname As Variant
>    Dim wb As Workbook
>    fname = Application.GetOpenFilename

(filefilter:="Excel Files (*.xls), *.xls")
Quote:
>    If fname <> False Then
>        Set wb = Workbooks.Open(fname)
>    End If
>    MsgBox "your code"
>    wb.Close
>End Sub

>you can use WB now instead of the workbook name to do
your things
>In this example it will close the file after the MsgBox

>--
>Regards Ron de Bruin
>(Win XP Pro SP-1  XL2002 SP-2)
>www.rondebruin.nl




- Show quoted text -

Quote:
>> Hi again
>> I'm trying to open a workbook from within another WB.  
At
>> present, I have:

>> Dim WB As Workbook
>> Set WB = Workbooks.Open("E:\Horizon\Quote
Sheets\Material
>> and Labour Pricing.xls")

>> and it works great, as long as the filename is the same.
>> But what if the filename changes?  Then I've tried this:

>> dlganswer = Application.Dialogs(xlDialogOpen).Show

>> which also works great as it allows me to select the WB.
>> The only problem is that I have to switch between the
WBs
>> to copy and paste, and then close the other one.  I
have a
>> statement to go back to the other WB:

>> Windows("Materials and Labour Pricing.xls").Activate

>> This works as long as the filename is the same.  I would
>> like it to read something like:

>> Windows(WB).Activate

>> which would solve my problem if I could get the initial
>> statements to work.  I've tried merging the two
statements:

>> Dim WB As Workbook
>> Set WB = Application.Dialogs(xlDialogOpen).Show

>> but all I get is "Compile error, type mismatch".  I'm
not
>> understanding the error or what is needed.  I've tried
>> several different things with no success.  I'm hoping
that
>> someone is able to steer me in the right direction.
>> Thanks.

>> Art

>.



Fri, 25 Nov 2005 00:38:35 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Macro to Copy 2 Wb Ranges into 3rd Wb.

2. Excel X crashes on opening pwd protected WB

3. Opening a WB containing remote DDE Links

4. Checking whether another user has a shared WB open

5. Q: how to open new WB with just one sheet

6. How to prevent the Update Link-question , when opening a wb

7. Open a Wb without raising the Wb_Open event.

8. Opening QP wb.3 worksheet

9. Opening a WB to a specific (the next empty)cell

10. Large Excel WB won't open


 
Powered by phpBB® Forum Software © phpBB Group