Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying an open workbook / sheet
My process fails - error 9: subscript out of range at this statement:-
With Workbooks(Cranafile).Worksheets(SheetName) on one PC! On all other PC's the macro works fine. The workbook defined by cranafile is definately open and definately has a sheet called sheetname. On my PC I can have the string defined in any case (upper or lower) and all is OK with or without the .xls extension. On this maveric PC I've had to add code to ensure the book and sheet names are exactly as held in workbooks(<bookid).name to make the with or any subsequent code find the book and sheet. Why is this ? The maveric PC in on windows NT (I think) and has recently had Excel re-installed. Is there a known bug in Excel here or in there some parameter I need to set ? This was a real pain to find and code round so any advice would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying an open workbook / sheet
I think it's more of a windows/code problem than excel.
There's a windows setting that hides/displays file extensions. If you don't specify the extension, then you code could fail (depending on this setting). If you include the extension, then it'll work all the time--no matter what the setting. so instead of: cranafile = "book1" use cranafile = "book1.xls" (well, that's my guess!) John Pritchard wrote: My process fails - error 9: subscript out of range at this statement:- With Workbooks(Cranafile).Worksheets(SheetName) on one PC! On all other PC's the macro works fine. The workbook defined by cranafile is definately open and definately has a sheet called sheetname. On my PC I can have the string defined in any case (upper or lower) and all is OK with or without the .xls extension. On this maveric PC I've had to add code to ensure the book and sheet names are exactly as held in workbooks(<bookid).name to make the with or any subsequent code find the book and sheet. Why is this ? The maveric PC in on windows NT (I think) and has recently had Excel re-installed. Is there a known bug in Excel here or in there some parameter I need to set ? This was a real pain to find and code round so any advice would be appreciated. Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying an open workbook / sheet
Thanks Dave - sounds like this is what was happening. I'll have a hunt round
for the windows setting to see if it makes the difference. "Dave Peterson" wrote: I think it's more of a windows/code problem than excel. There's a windows setting that hides/displays file extensions. If you don't specify the extension, then you code could fail (depending on this setting). If you include the extension, then it'll work all the time--no matter what the setting. so instead of: cranafile = "book1" use cranafile = "book1.xls" (well, that's my guess!) John Pritchard wrote: My process fails - error 9: subscript out of range at this statement:- With Workbooks(Cranafile).Worksheets(SheetName) on one PC! On all other PC's the macro works fine. The workbook defined by cranafile is definately open and definately has a sheet called sheetname. On my PC I can have the string defined in any case (upper or lower) and all is OK with or without the .xls extension. On this maveric PC I've had to add code to ensure the book and sheet names are exactly as held in workbooks(<bookid).name to make the with or any subsequent code find the book and sheet. Why is this ? The maveric PC in on windows NT (I think) and has recently had Excel re-installed. Is there a known bug in Excel here or in there some parameter I need to set ? This was a real pain to find and code round so any advice would be appreciated. Thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identifying an open workbook / sheet
In win98, I'd do this:
Start windows explorer View Tab|Uncheck the "hide file extensions for known file types" John Pritchard wrote: Thanks Dave - sounds like this is what was happening. I'll have a hunt round for the windows setting to see if it makes the difference. "Dave Peterson" wrote: I think it's more of a windows/code problem than excel. There's a windows setting that hides/displays file extensions. If you don't specify the extension, then you code could fail (depending on this setting). If you include the extension, then it'll work all the time--no matter what the setting. so instead of: cranafile = "book1" use cranafile = "book1.xls" (well, that's my guess!) John Pritchard wrote: My process fails - error 9: subscript out of range at this statement:- With Workbooks(Cranafile).Worksheets(SheetName) on one PC! On all other PC's the macro works fine. The workbook defined by cranafile is definately open and definately has a sheet called sheetname. On my PC I can have the string defined in any case (upper or lower) and all is OK with or without the .xls extension. On this maveric PC I've had to add code to ensure the book and sheet names are exactly as held in workbooks(<bookid).name to make the with or any subsequent code find the book and sheet. Why is this ? The maveric PC in on windows NT (I think) and has recently had Excel re-installed. Is there a known bug in Excel here or in there some parameter I need to set ? This was a real pain to find and code round so any advice would be appreciated. Thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
open to a certain sheet tab in a different workbook | New Users to Excel | |||
Search open sheets in workbook and insert into open sheet | Excel Discussion (Misc queries) | |||
Open workbook on same sheet? | Excel Discussion (Misc queries) | |||
Open workbook at a particular sheet | New Users to Excel | |||
open sheet on workbook open | Excel Programming |