Set a reference to another workbook

[nigelk]

Hi all

I have the following code

Workbooks.Open FileName:="C:\Sports\Golf.xlsm"

which works fine, however, as the the workbook is already open all I want to do is set a reference to it so I can write to it later. This gives a "Subcript out of range" error (Sportbook is dimmed as a workbook)

  Set Sportbook = Application.Workbooks("C:\Sports\Golf.xlsm")

What am I doing wrong?

 

[NdNoviceHlp]

HTH. Dave

Dim Ofsobj As Object, NewWb As Object
Set Ofsobj = CreateObject("Scripting.FileSystemObject")
Set NewWb = Ofsobj.GetFile("C:\Sports\Golf.xlsm")
Workbooks.Open Filename:=NewWb
'to use example
Workbooks(NewWb.Name).Sheets("sheet1").Cells(1, "A").Value = _
          ThisWorkbook.Sheets("sheet1").Cells(1, "A").Value
Set NewWb = Nothing
Set Ofsobj = Nothing