Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript out of Range | Excel Discussion (Misc queries) | |||
what does 'Subscript Out of range' mean?? | Excel Worksheet Functions | |||
9: Subscript out of range | Excel Discussion (Misc queries) | |||
Subscript out of range error | Excel Programming |