Why can't I define a workbook either of these ways? (I have the range bit in there just for a quick test.) How do I fix it?
This produces a "Compile Error: Type Mismatch"
Sub Setwbk()
Dim wbk As Workbook
Set wbk = "F:\Quarterly Reports\2012 Reports\New Reports\ _
Master Benchmark Data Sheet.xlsx"
Range("A2") = wbk.Name
End Sub
And this creates a "Runtime Error '91': Object variable or with block variable not set"
Sub Setwbk()
Dim wbk As Workbook
wbk = "F:\Quarterly Reports\2012 Reports\New Reports\ _
Master Benchmark Data Sheet.xlsx"
Range("A2") = wbk.Name
End Sub
What am I missing here? I've been hammering away at VBA for a month, gotten pretty sophisticated, but this has me stumped. I'm missing something elemental.
I just want to define a workbook so I don't have to type it all out!
It's actually a sensible question. Here's the answer from Excel 2010 help:
"The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel."
So, since that workbook isn't open - at least I assume it isn't - it can't be set as a workbook object. If it was open you'd just set it like:
Set wbk = workbooks("Master Benchmark Data Sheet.xlsx")
You'll need to open the workbook to refer to it.
Sub Setwbk()
Dim wbk As Workbook
Set wbk = Workbooks.Open("F:\Quarterly Reports\2012 Reports\New Reports\ _
Master Benchmark Data Sheet.xlsx")
End Sub
* Follow Doug's answer if the workbook is already open. For the sake of making this answer as complete as possible, I'm including my comment on his answer:
Why do I have to "set" it?
Set
is how VBA assigns object variables. Since a Range
and a Workbook
/Worksheet
are objects, you must use Set
with these.
Source: Stackoverflow.com