How to set workbook reference - basics

[deko]

I'm using late binding, and for some reason I'm getting lost trying to set a
reference to a Workbook object:

Dim xlapp As Object
Dim xlwkbs As Object
Dim xlwkb As Object
Set xlapp = CreateObject("Excel.Application")
Set xlwkbs = xlapp.Workbooks

so far so good (I think), but when I try to set a reference to a Workbook, I
get a "Subscript out of range" error

Set xlwkb = xlwkbs(strPathToFile)

What I want to do is set the reference and then pass xlwkb to another
function and do stuff like add worksheets. Do I need to set the reference
differently?

Thanks in advance.

 

[Tom Ogilvy]

if strPathtoFile is something like "C:\MyFolcer\MyFile.xls" then that is
your problem.

the argument to Workbooks must be of the form Myfile.xls
and Myfile.xls must be open in excel.

 

[deko]

if strPathtoFile is something like "C:\MyFolcer\MyFile.xls" then that is

your problem.

the argument to Workbooks must be of the form Myfile.xls
and Myfile.xls must be open in excel.

Thanks for the reply.

I'm wondering if I need to create a worksheet object first:

Set xlwks = CreateObject("Excel.Sheet")

Is this correct? And then get the workbook like this:

Set xlwkb = xlwks.Parent

I think once I have the objects set, I'll be on the right track. I might
have to go back to early binding otherwise...

 

[Tom Ogilvy]

Dim xlapp As Object
Dim xlwkbs As Object
Dim xlwkb As Object
Set xlapp = CreateObject("Excel.Application")
' Set xlwkbs = xlapp.Workbooks
strPathtoFile = "C:\MyFolder\MyFile.xls"
Set xlwkbk = xlApp.workbooks.Open(strPathtoFile)
set xlwks = xlwkbk.Worksheets("Sheet1")

or

set xlwkbk = xlApp.Workbooks.Add
set xlwks = xlwkbk.Worksheets("Sheet1")
The workbook will be added with the number of sheets set in tools options
but will have at least one worksheet as a minimum.