Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA application; Subscript Out of Range
Hi I made a VBA application in Excel 2000 with VB 6 on my machine. However, when I transferred the necessary files to another coworkers machine and ran the macro, I received a "Subcript Out of Range" error when I try to open up another Excel worksheet within the macro. Any assistance on this issue? The coworker's machine is running Excel 2002... I dont think this is an issue... or is it? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA application; Subscript Out of Range
It is unlikely that your problem is the version of VBA/Excel you
coworker is using. The most likely explanation based on what I know s far, which isn't much, is that the file your code is opening is on you network and the network drive where it is located is mapped differentl on your coworker's computer. Check that out and let me know how tha works out. - Piku -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA application; Subscript Out of Range
Hi
My problem occurs when I click a button to open another excel file from a macro within a separate excel file. I think the code and error occurs simply from the following lines of code: On Error GoTo ErrorHandler: filepath = ActiveWorkbook.Path & "\" & filename & ".xls" oApp1.Workbooks.Open filepath I am not using any network mappings to find the files. In fact, I saved all the files on the local C:\ on my coworkers computer. When I click the button, I get a "Subscript out of Range" error and for some reason, the excel file I'm tryin to open gets locked for editing. ?? -----Original Message----- It is unlikely that your problem is the version of VBA/Excel your coworker is using. The most likely explanation based on what I know so far, which isn't much, is that the file your code is opening is on your network and the network drive where it is located is mapped differently on your coworker's computer. Check that out and let me know how that works out. - Pikus --- Message posted from http://www.ExcelForum.com/ . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA application; Subscript Out of Range
If you activeworkbook is also in C:\ as you seem to indicate, then the
string you build for filepath will be "C:\\filename.xls" since the Activeworkbook.Path for a file in the root directory includes the "\" on the end. filePath = Activeworkbook.Path if left(filePath,1) < "\" then filePath = filepath & "\" filePath = filepath & filename & ".xls" NT based systems don't seem to be bothered by the \\ (from what I have seen), but windows 9.x will break. -- Regards, Tom Ogilvy "M J" wrote in message ... Hi My problem occurs when I click a button to open another excel file from a macro within a separate excel file. I think the code and error occurs simply from the following lines of code: On Error GoTo ErrorHandler: filepath = ActiveWorkbook.Path & "\" & filename & ".xls" oApp1.Workbooks.Open filepath I am not using any network mappings to find the files. In fact, I saved all the files on the local C:\ on my coworkers computer. When I click the button, I get a "Subscript out of Range" error and for some reason, the excel file I'm tryin to open gets locked for editing. ?? -----Original Message----- It is unlikely that your problem is the version of VBA/Excel your coworker is using. The most likely explanation based on what I know so far, which isn't much, is that the file your code is opening is on your network and the network drive where it is located is mapped differently on your coworker's computer. Check that out and let me know how that works out. - Pikus --- Message posted from http://www.ExcelForum.com/ . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA application; Subscript Out of Range
Actually I tried that and I also tried hardocding a file
name and I still get the same "Subscript Out of Range" error when I try to open the excel file from the macro. Anything else? -----Original Message----- If you activeworkbook is also in C:\ as you seem to indicate, then the string you build for filepath will be "C:\\filename.xls" since the Activeworkbook.Path for a file in the root directory includes the "\" on the end. filePath = Activeworkbook.Path if left(filePath,1) < "\" then filePath = filepath & "\" filePath = filepath & filename & ".xls" NT based systems don't seem to be bothered by the \\ (from what I have seen), but windows 9.x will break. -- Regards, Tom Ogilvy "M J" wrote in message ... Hi My problem occurs when I click a button to open another excel file from a macro within a separate excel file. I think the code and error occurs simply from the following lines of code: On Error GoTo ErrorHandler: filepath = ActiveWorkbook.Path & "\" & filename & ".xls" oApp1.Workbooks.Open filepath I am not using any network mappings to find the files. In fact, I saved all the files on the local C:\ on my coworkers computer. When I click the button, I get a "Subscript out of Range" error and for some reason, the excel file I'm tryin to open gets locked for editing. ?? -----Original Message----- It is unlikely that your problem is the version of VBA/Excel your coworker is using. The most likely explanation based on what I know so far, which isn't much, is that the file your code is opening is on your network and the network drive where it is located is mapped differently on your coworker's computer. Check that out and let me know how that works out. - Pikus --- Message posted from http://www.ExcelForum.com/ . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA application; Subscript Out of Range
Is it possible that "filename" already includes the ".xls"? And did you
verify that the other file actually is an xls file? Just a thought... - Pikus --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA application; Subscript Out of Range
oApp1.Workbooks.Open filepath
won't produce subscript out of range to the best of my knowledge. The problem with getting subscript out of range on another computer is when you don't put the extension on the filename in the workbooks collection. If you do set wkbk = Workbooks("Myfile") vice set wkbk = Workbooks("Myfile.xls") you can get a subscript out of range error using the first syntax based on the status of whether extenions are hidden or not setting. Perhaps that is the source of your problem. -- Regards, Tom Ogilvy "M J" wrote in message ... Actually I tried that and I also tried hardocding a file name and I still get the same "Subscript Out of Range" error when I try to open the excel file from the macro. Anything else? -----Original Message----- If you activeworkbook is also in C:\ as you seem to indicate, then the string you build for filepath will be "C:\\filename.xls" since the Activeworkbook.Path for a file in the root directory includes the "\" on the end. filePath = Activeworkbook.Path if left(filePath,1) < "\" then filePath = filepath & "\" filePath = filepath & filename & ".xls" NT based systems don't seem to be bothered by the \\ (from what I have seen), but windows 9.x will break. -- Regards, Tom Ogilvy "M J" wrote in message ... Hi My problem occurs when I click a button to open another excel file from a macro within a separate excel file. I think the code and error occurs simply from the following lines of code: On Error GoTo ErrorHandler: filepath = ActiveWorkbook.Path & "\" & filename & ".xls" oApp1.Workbooks.Open filepath I am not using any network mappings to find the files. In fact, I saved all the files on the local C:\ on my coworkers computer. When I click the button, I get a "Subscript out of Range" error and for some reason, the excel file I'm tryin to open gets locked for editing. ?? -----Original Message----- It is unlikely that your problem is the version of VBA/Excel your coworker is using. The most likely explanation based on what I know so far, which isn't much, is that the file your code is opening is on your network and the network drive where it is located is mapped differently on your coworker's computer. Check that out and let me know how that works out. - Pikus --- Message posted from http://www.ExcelForum.com/ . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA application; Subscript Out of Range
Tom,
Not having specifying the extension IS my problem. I've been working to correct it when I come across it. Unfortunately, I've written a lot of macros where I use Workbooks("Myfile") instead of Workbooks("Myfile.xls") and do not always have the time to fix them the moment I need to run the macro. Is there an excel setting that I can set on my machine to get it to accept Workbooks("Myfile") - - it used to do this up until about 1 month ago. thanks, Murray Williams -----Original Message----- oApp1.Workbooks.Open filepath won't produce subscript out of range to the best of my knowledge. The problem with getting subscript out of range on another computer is when you don't put the extension on the filename in the workbooks collection. If you do set wkbk = Workbooks("Myfile") vice set wkbk = Workbooks("Myfile.xls") you can get a subscript out of range error using the first syntax based on the status of whether extenions are hidden or not setting. Perhaps that is the source of your problem. -- Regards, Tom Ogilvy "M J" wrote in message ... Actually I tried that and I also tried hardocding a file name and I still get the same "Subscript Out of Range" error when I try to open the excel file from the macro. Anything else? -----Original Message----- If you activeworkbook is also in C:\ as you seem to indicate, then the string you build for filepath will be "C:\\filename.xls" since the Activeworkbook.Path for a file in the root directory includes the "\" on the end. filePath = Activeworkbook.Path if left(filePath,1) < "\" then filePath = filepath & "\" filePath = filepath & filename & ".xls" NT based systems don't seem to be bothered by the \\ (from what I have seen), but windows 9.x will break. -- Regards, Tom Ogilvy "M J" wrote in message ... Hi My problem occurs when I click a button to open another excel file from a macro within a separate excel file. I think the code and error occurs simply from the following lines of code: On Error GoTo ErrorHandler: filepath = ActiveWorkbook.Path & "\" & filename & ".xls" oApp1.Workbooks.Open filepath I am not using any network mappings to find the files. In fact, I saved all the files on the local C:\ on my coworkers computer. When I click the button, I get a "Subscript out of Range" error and for some reason, the excel file I'm tryin to open gets locked for editing. ?? -----Original Message----- It is unlikely that your problem is the version of VBA/Excel your coworker is using. The most likely explanation based on what I know so far, which isn't much, is that the file your code is opening is on your network and the network drive where it is located is mapped differently on your coworker's computer. Check that out and let me know how that works out. - Pikus --- Message posted from http://www.ExcelForum.com/ . . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA application; Subscript Out of Range
It isn't an excel setting. It is a setting under folder options (accessed
from various locations, usually including Start=Settings) hide known file extensions I believe. -- Regards, Tom Ogilvy Murray Williams wrote in message ... Tom, Not having specifying the extension IS my problem. I've been working to correct it when I come across it. Unfortunately, I've written a lot of macros where I use Workbooks("Myfile") instead of Workbooks("Myfile.xls") and do not always have the time to fix them the moment I need to run the macro. Is there an excel setting that I can set on my machine to get it to accept Workbooks("Myfile") - - it used to do this up until about 1 month ago. thanks, Murray Williams -----Original Message----- oApp1.Workbooks.Open filepath won't produce subscript out of range to the best of my knowledge. The problem with getting subscript out of range on another computer is when you don't put the extension on the filename in the workbooks collection. If you do set wkbk = Workbooks("Myfile") vice set wkbk = Workbooks("Myfile.xls") you can get a subscript out of range error using the first syntax based on the status of whether extenions are hidden or not setting. Perhaps that is the source of your problem. -- Regards, Tom Ogilvy "M J" wrote in message ... Actually I tried that and I also tried hardocding a file name and I still get the same "Subscript Out of Range" error when I try to open the excel file from the macro. Anything else? -----Original Message----- If you activeworkbook is also in C:\ as you seem to indicate, then the string you build for filepath will be "C:\\filename.xls" since the Activeworkbook.Path for a file in the root directory includes the "\" on the end. filePath = Activeworkbook.Path if left(filePath,1) < "\" then filePath = filepath & "\" filePath = filepath & filename & ".xls" NT based systems don't seem to be bothered by the \\ (from what I have seen), but windows 9.x will break. -- Regards, Tom Ogilvy "M J" wrote in message ... Hi My problem occurs when I click a button to open another excel file from a macro within a separate excel file. I think the code and error occurs simply from the following lines of code: On Error GoTo ErrorHandler: filepath = ActiveWorkbook.Path & "\" & filename & ".xls" oApp1.Workbooks.Open filepath I am not using any network mappings to find the files. In fact, I saved all the files on the local C:\ on my coworkers computer. When I click the button, I get a "Subscript out of Range" error and for some reason, the excel file I'm tryin to open gets locked for editing. ?? -----Original Message----- It is unlikely that your problem is the version of VBA/Excel your coworker is using. The most likely explanation based on what I know so far, which isn't much, is that the file your code is opening is on your network and the network drive where it is located is mapped differently on your coworker's computer. Check that out and let me know how that works out. - Pikus --- Message posted from http://www.ExcelForum.com/ . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
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. | Excel Programming | |||
Subscript Out of Range | Excel Programming |