Subscript Out of Range
 

This one has me totally stumped........

The following line of code:
Workbooks.Open FileName:="C:\DATA\FOXPRO\POOLAVG\PLFALL03.DBF"
does indeed work and it works in my sub which is attached.

What I'm trying to do is make my workbook more flexible so instead of
hard coding the "PLFALL03" in the code, I placed it in a cell.
Worksheets("Utilities").Range("B6") = PLFALL03

So I tried the following:
Workbooks.Open FileName:="C:\DATA\FOXPRO\POOLAVG\" &
Worksheets("Utilities").Range("B6").Text & ".DBF"
but it crashes?????

If I copy and paste that exact line of code to the immediate window.
it opens the PLFALL03.DBF file???

The complete sub that I'm using this in follows. Any help/insight on what
might be wrong would be appreciated (this does not make any sense
at all to me). (Win XP/XL2000)

Thanks,
John

Sub GetIndivStandings()
' Check to see if Sheet4 exists and if not, create it.
Dim WS As Worksheet
On Error Resume Next
Set WS = Worksheets("Sheet4")
On Error GoTo 0
If WS Is Nothing Then
Set WS = Worksheets.Add
WS.Name = "Sheet4"
End If
' Open the FoxPro database file
' Database may not have been created. Suppress errors in that event.
' On Error GoTo NotExistYet
' The above line would not normally be commented'
' I uncommented to see where the error was and get a Subscript Out
' of range on the following line
Workbooks.Open FileName:="C:\DATA\FOXPRO\POOLAVG\" &
Worksheets("Utilities").Range("B6").Text & ".DBF"
' Copy the entire range to the clipboard
Range("A1").CurrentRegion.Copy
' Activate the "SaveasName" workbook
Windows(SaveAsName).Activate
' Select Sheet4
Worksheets("Sheet4").Activate
' Paste contents of the clipboard
Range("A1").Select
ActiveSheet.Paste
' Clear the clipboard
Application.CutCopyMode = False
' Activate the newly opened database file and close it.
Windows("PLFALL03.DBF").Activate ' Need to update this too
ActiveWorkbook.Close
On Error GoTo 0
Exit Sub
NotExistYet:
On Error GoTo 0
MsgBox "Individual Standings file not found"
End Sub

Subscript Out of Range
 

Workbooks.Open FileName:="C:\DATA\FOXPRO\POOLAVG\" &
Worksheets("Utilities").Range("B6").Text & ".DBF"

subscript out of range, would indicate that the activeworkbook, at the time
the code is executed, does not have a worksheet named Utilities.

executing it in the immediate window successfully would mean the
activeworkbook at that point does.

Perhaps qualify
Worksheets("Utilities").Range("B6").Text & ".DBF"

with

Thisworkbook.Worksheets("Utilities").Range("B6").T ext & ".DBF"

or the appropriate workbook reference.

--
Regards,
Tom Ogilvy


John Wilson wrote in message
...
This one has me totally stumped........

The following line of code:
Workbooks.Open FileName:="C:\DATA\FOXPRO\POOLAVG\PLFALL03.DBF"
does indeed work and it works in my sub which is attached.

What I'm trying to do is make my workbook more flexible so instead of
hard coding the "PLFALL03" in the code, I placed it in a cell.
Worksheets("Utilities").Range("B6") = PLFALL03

So I tried the following:
Workbooks.Open FileName:="C:\DATA\FOXPRO\POOLAVG\" &
Worksheets("Utilities").Range("B6").Text & ".DBF"
but it crashes?????

If I copy and paste that exact line of code to the immediate window.
it opens the PLFALL03.DBF file???

The complete sub that I'm using this in follows. Any help/insight on what
might be wrong would be appreciated (this does not make any sense
at all to me). (Win XP/XL2000)

Thanks,
John

Sub GetIndivStandings()
' Check to see if Sheet4 exists and if not, create it.
Dim WS As Worksheet
On Error Resume Next
Set WS = Worksheets("Sheet4")
On Error GoTo 0
If WS Is Nothing Then
Set WS = Worksheets.Add
WS.Name = "Sheet4"
End If
' Open the FoxPro database file
' Database may not have been created. Suppress errors in that event.
' On Error GoTo NotExistYet
' The above line would not normally be commented'
' I uncommented to see where the error was and get a Subscript Out
' of range on the following line
Workbooks.Open FileName:="C:\DATA\FOXPRO\POOLAVG\" &
Worksheets("Utilities").Range("B6").Text & ".DBF"
' Copy the entire range to the clipboard
Range("A1").CurrentRegion.Copy
' Activate the "SaveasName" workbook
Windows(SaveAsName).Activate
' Select Sheet4
Worksheets("Sheet4").Activate
' Paste contents of the clipboard
Range("A1").Select
ActiveSheet.Paste
' Clear the clipboard
Application.CutCopyMode = False
' Activate the newly opened database file and close it.
Windows("PLFALL03.DBF").Activate ' Need to update this too
ActiveWorkbook.Close
On Error GoTo 0
Exit Sub
NotExistYet:
On Error GoTo 0
MsgBox "Individual Standings file not found"
End Sub

Subscript Out of Range
 

Tom,

I'm sure that you've heard this many times before but you are
absolutely @#$%^&*) amazing (meant in a nice way).

Oh, and by the way.....
Your suggestion obviously did work like a charm.

Thanks,
John

"Tom Ogilvy" wrote in message
...
Workbooks.Open FileName:="C:\DATA\FOXPRO\POOLAVG\" &
Worksheets("Utilities").Range("B6").Text & ".DBF"

subscript out of range, would indicate that the activeworkbook, at the
time
the code is executed, does not have a worksheet named Utilities.

executing it in the immediate window successfully would mean the
activeworkbook at that point does.

Perhaps qualify
Worksheets("Utilities").Range("B6").Text & ".DBF"

with

Thisworkbook.Worksheets("Utilities").Range("B6").T ext & ".DBF"

or the appropriate workbook reference.

--
Regards,
Tom Ogilvy