If there is no open workbook named workbook1.xls, then this will fail:
set wbk = workbooks("workbook1.xls")
If there is a workbook with that name that's already open (by the user???), then
it will work ok.

If you don't have a workbook with that name open and you want to open it, then
you'd use that second version.

It really depends on where you're starting.

If you (as the developer) don't know if the user already opened that
workbook--but your program needs it, you could combine them...

Dim wkbk as workbook
dim wkbkName as string
dim wkbkPath as string

wkbkname = "workbook1.xls"
wkbkpath = "C:\documents\" '<-- include that trailing backslash!

set wkbk = nothing
on error resume next
set wkbk = workbooks(wkbkname)
on error goto 0

if wkbk is nothing then
'it wasn't set correctly, so it's not open.
'so try to open it!
on error resume next
set wkbk = workbooks.open(filename:=wkbkpath & wkbkname)
on error goto 0
end if

if wkbk is nothing then
msgbox wkbkname & " wasn't opened and couldn't be found in " & wkbkpath
exit sub '????
end if

msgbox "It's ready to be used!


sam wrote:

Hey Dave, Thanks for the help.
I still get a Subscript out of range error if i use:
Set wbk = Workbooks("WorkBook1.xls")

But this works fine:
Set wbk = Workbooks.open("C:\Documents\WorkBook1.xls")

should it be this way?

Thanks in advance

"Dave Peterson" wrote:

To use this line:
Set wbk = Workbooks("C:\Documents\WorkBook1")
The workbook has to be open already.

And you don't include the drive or path.

This may fail:
Set wbk = Workbooks("WorkBook1")
This will always work:
Set wbk = Workbooks("WorkBook1.xls")



sam wrote:

Hi All, I am getting subscript out of range error on this line:

---------------Set wbk = Workbooks("C:\Documents\WorkBook1")

here is my code

Sub Submit_Click()

Dim wbk As Workbook
Dim ws As Worksheet
Dim FName As String

Set wbk = Workbooks("C:\Documents\WorkBook1")

With Worksheets("Sheet1")

Range("A1").Value = Me.Name.Value

End With

FName = "C:\Documents\" _
& "File_of" & "-" & Me.Name.Value & ".xls"

Application.DisplayAlerts = False
With wbk

.SaveAs Filename:=FName

.Close

End With

end sub

Thanks in advance

--

Dave Peterson


--

Dave Peterson