Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

vba

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!

like image 225
Tommy Z Avatar asked Jun 15 '12 16:06

Tommy Z


People also ask

What is a workbook object?

The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel. The ThisWorkbook property of the Application object returns the workbook where the Visual Basic code is running.

How do you define an object in VBA?

You can declare an object variable with the Object data type when the specific object type is not known until the procedure runs. Use the Object data type to create a generic reference to any object. Dim MyObject As Object ' Declared as generic object. Dim MyObject As Sample ' Declared only as Sample object.


2 Answers

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")
like image 183
Doug Glancy Avatar answered Oct 28 '22 08:10

Doug Glancy


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.

like image 29
Gaffi Avatar answered Oct 28 '22 08:10

Gaffi