set a workbook variable

[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?

 

[Gary''s Student]

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

 

[dstiefe]

so you have to first open the workbook to do it?

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

 

[Gary''s Student]

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.

 

[Dave Peterson]

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.