ERROR: subscript out of range

[sam]

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

 

[ryguy7272]

Something doesn't look right. Maybe this:
Set wbk = Workbooks("C:\Documents\WorkBook1\")

HTH,
Ryan---

 

[Gary''s Student]

Getting a work book this way will only work if the workbook is already open.
Try:

Set wbk = Workbooks("WorkBook1")
or
Set wbk = Workbooks("WorkBook1.xls")

 

[Jacob Skaria]

'If the workbook is not open
Set wbk = Workbooks.Open("C:\geo13.xls")

'If the workbook is already open (mention only the xls name..If not saved it
would be like book1, book2 etc; withoutt the extension)
Set wbk = Workbooks("geo13.xls")

If this post helps click Yes

 

[Dave Peterson]

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]

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]

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!