Why can t I define my workbook as an object

Question

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

User · Accepted Answer

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

User · Answer

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

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

Examples related to vba

Examples related to excel