Set Workbook from Path

[cha]

Hi,

In order for me to access workbooks, I have to first have them saved in the same directory as the book I'm currently working in, then I have to make a call to open them. Then later, I close them. I know there has to be an easier way.

 Workbooks.Open ("W:\Everyone\Reference.xls")
    Set GlobalCourses_file = Workbooks("W:\Everyone\Reference.xls")

 

[shg]

In order for me to access workbooks, I have to first have them saved in the same directory as the book I'm currently working in, then I have to make a call to open them.

What leads you to believe that?

    Dim wkb As Workbook
    
    Set wkb = Workbooks.Open("W:\anypath\anyfile.xls")

 

[lokiluke]

Unless you dont have permission to access the directory you should be able to open it from different folders E.G.

Workbooks.Open Filename:="C:\some folder\some workbook.xls"

i use this with a variable to open a workbook in one of my macros:

On Error GoTo errOpenStatusSheet
            Workbooks.Open Filename:=strStatusSheetLocation & strStatusSheetFilename, UpdateLinks:=2, ReadOnly:=True
            On Error GoTo ErrorHandler

 

[cha]

Let me be more clear...
What I really mean to ask is do the workbooks need to be open for me to set them as objects?

Why can't I do this:

set wkb = Workbooks("W:\something.xls")

 

[shg]

From Help:

The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel.

 

[cha]

that definitely clears things up . Thanks shg