Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Opening a workbook, and setting it as a variable

  Asked By: Can    Date: Jan 11    Category: MS Office    Views: 1001
  

I need to open an existing workbook, and then I need to set it's value
to a variable. I can't use the index number because I don't know
exactly how many workbooks are open.

I've tried a few ways, none successful:
1) application.Dialogs(xlDialogOpen).Show
2) Set master = Application.Dialogs(xlDialogOpen)
3) Application.Dialogs(xlDialogOpen).Show
Set master = activebook

Is there an object that's similar to 'ActiveSheet' that refers to a
workbook?

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Mehreen Malik     Answered On: Jan 11

Yes it's called ActiveWorkbook.

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

(if the user presses the cancel button the active workbook  will remain
the one that was active before the dialogue box was shown, so mywb may
not end up being the workbook you expect)

 
Answer #2    Answered By: Daya Sharma     Answered On: Jan 11

Thanks for the help. But if they click 'Cancel', I want to end the
program. What value is returned if the cancel button is clicked?

 
Answer #3    Answered By: Viren Rajput     Answered On: Jan 11

Cancel returns the text string "False". Here is an amended version of Pascal's
code:

Sub AAAAA()
Dim mywb As Workbook, retval
retval = Application.Dialogs(xlDialogOpen).Show
If retval = "False" Then
MsgBox "Cancelled by user"
Exit Sub
End If
Set mywb = ActiveWorkbook
End Sub

 
Answer #4    Answered By: Cheryl Murphy     Answered On: Jan 11

ActiveWorkbook didn't do it for you?

 
Didn't find what you were looking for? Find more on Opening a workbook, and setting it as a variable Or get search suggestion and latest updates.




Tagged:        

 
 
 
 

Related Post