[vba] Why can't I define my workbook as an object?

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!

This question is related to vba excel

The answer is


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.