set a workbook variable

  • Thread starter Thread starter dstiefe
  • Start date Start date
D

dstiefe

I would like to set my variable "wb" to a workbook object

so something like this

dim wb as workbook

set wb = ????

I would like to set the variable "wb" to a workbook that is not open and in
a different folder...can i do that?
 
I would set the path/filename to a string and then set the workbook name
after it is opened:

Sub Macro1()
Dim s As String
Dim wb As Workbook
s = "C:\test\beta.xls"
Workbooks.Open Filename:=s
Set wb = ActiveWorkbook
MsgBox (wb.Name)
MsgBox (wb.Path)
End Sub
 
so you have to first open the workbook to do it?

Gary''s Student said:
I would set the path/filename to a string and then set the workbook name
after it is opened:

Sub Macro1()
Dim s As String
Dim wb As Workbook
s = "C:\test\beta.xls"
Workbooks.Open Filename:=s
Set wb = ActiveWorkbook
MsgBox (wb.Name)
MsgBox (wb.Path)
End Sub
Click to expand...
 
That is what I usually do. That way I have both the path and filename in a
single variable. In my experience, I never have to set the WorkBook Object
before I open it.
 
You can do the assignment when you open the workbook. That makes it nice if
there are no visible windows (a hidden workbook or an addin).

Sub Macro1()
Dim s As String
Dim wb As Workbook
s = "C:\test\beta.xls"
set wb = Workbooks.Open(Filename:=s)
MsgBox wb.Name & vblf & wb.path & vblf & wb.fullname
End Sub

But the workbook does have to be open to be part of the workbooks collection.
 
Back
Top