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
Open up a WB