Script out of range

R

ranswrt

I have the following code:

Sub ReturnToBldrsEst()
Dim wbfilename As String

Call checkworkbook
wbfilename = Worksheets("Current DB").Range("currentwb")
Workbooks(wbfilename).Activate

End Sub
Sub checkworkbook()
Dim wb As Workbook
Dim wbopen As Boolean
Dim wbfilename As String

wbopen = False
wbfilename = Worksheets("Current DB").Range("currentwb")

If wbfilename <> "" Then
For Each wb In Application.Workbooks
If wb.FullName = wbfilename Then
wbopen = True
End If
Next
End If
If wbopen = False Then
Workbooks.Open (wbfilename)
End If
End Sub

On the line with "Workbooks(wbfilename).Activate" I get a script out of
range error. wbfilename =C:\Documents and Settings\Randy\Desktop\Builders
Estimator.xls

What am I doing wrong here?
Thanks
 
D

Dave Peterson

When you use the workbooks() collection, you don't include the drive and
path--just the plain old filename (Builders Estimator.xls, in this case).
 
R

ranswrt

Thank you

Dave Peterson said:
When you use the workbooks() collection, you don't include the drive and
path--just the plain old filename (Builders Estimator.xls, in this case).
 
J

JLGWhiz

wbfilename = Worksheets("Current DB").Range("currentwb")
Workbooks(wbfilename).Activate

What happens here is:
You assign a named worksheet range to the variable wbfilename.
Then you try to use that named range a workbook file name.
Unless you coincidentally use the same name for the range and a workbook,
you are going to get a subscript out of range message, because VBA will not
be able to find the Range Name in the directory for the file names.
Also, It will not let you Acivate a range, without first activating the
worksheet. It would be better to learn to write code without using the
select and activate commands, at least it was for me.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top