Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Script out of range error
Hi,
I'm not sure why I'm getting an error 'run-time error '9': script out of range' at Windows(qfFile).Activate Here is the code: Sub ProcessCS() Const qfPath = "C:\Documents and Settings\John\My Documents\quoteprogramfiles\" Const qrPath = "C:\Documents and Settings\John\My Documents\CSQuotes\" Const qrFile = "CSQuoteReport.xls" ' Check if you are in the quote or a processed quote If isFile(qfPath & "CSQuoteForm.xls") = False Then response = MsgBox("This quote has already been processed. Do you want to create a new quote with a new quote number by copying this already processed quote?", _ vbYesNo + vbQuestion) qfFile = ThisWorkbook.Name Else response = vbNo qfFile = "CSQuoteForm.xls" End If Windows(qfFile).Activate Range("F3").Select ActiveCell = q I'm just a novice at this and would much appreciate the help. I'm reading a VB book, but need to know quickly for a job I'm doing. Sorry to be a pest. Also, the file personal.xls keeps randomly opening and if anyone has a hint where I can look to stop this, that would be great, too. I can't even find the file. Michele |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Script out of range error
If the user answers no to your prompt, then
qfFile = "CSQuoteForm.xls" and from the context of your code, I would assume there is no open file with that name. thus the subscript out of range error. -- Regards, Tom Ogilvy wrote in message ups.com... Hi, I'm not sure why I'm getting an error 'run-time error '9': script out of range' at Windows(qfFile).Activate Here is the code: Sub ProcessCS() Const qfPath = "C:\Documents and Settings\John\My Documents\quoteprogramfiles\" Const qrPath = "C:\Documents and Settings\John\My Documents\CSQuotes\" Const qrFile = "CSQuoteReport.xls" ' Check if you are in the quote or a processed quote If isFile(qfPath & "CSQuoteForm.xls") = False Then response = MsgBox("This quote has already been processed. Do you want to create a new quote with a new quote number by copying this already processed quote?", _ vbYesNo + vbQuestion) qfFile = ThisWorkbook.Name Else response = vbNo qfFile = "CSQuoteForm.xls" End If Windows(qfFile).Activate Range("F3").Select ActiveCell = q I'm just a novice at this and would much appreciate the help. I'm reading a VB book, but need to know quickly for a job I'm doing. Sorry to be a pest. Also, the file personal.xls keeps randomly opening and if anyone has a hint where I can look to stop this, that would be great, too. I can't even find the file. Michele |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Script out of range error
Hi,
If the answer is no, then "CSQuoteForm.xls" is the file from which the macro is being run and yes, the file is already open. If the answer is yes (which is where I'm having the problem), the file from which the macro is being run is qfFile = ThisWorkbook.Name. So the file should be open, too. You see the first time the macro runs, it saves the quote to a new file and the macro is still in the file. If the customer wants to bring up an existing quote, modify it and reprocess it (run the macro again), my macro needs to know what the new quote filename is to process further. Thanks for trying, but any other ideas would be great. Michele |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Script out of range error
What's the difference between ThisWorkbook and the file "from which the
macro is being run" In most everyone else's version of Excel, these would be the same file. So ThisWorkbook.Name should suffice and most definitely it should be open. You don't use the results of the msgbox in the code shown, so it is irrelevant whether it is Yes or No. The if structure is only dependent on the result of Isfile. (my mistatement there). In any event, you must be mistaken or not understand your code. There is no way Windows(qfFile).Activate would give a subscript out of range error if the value of qfFile is in fact ThisWorkbook.Name. -- Regards, Tom Ogilvy wrote in message ups.com... Hi, If the answer is no, then "CSQuoteForm.xls" is the file from which the macro is being run and yes, the file is already open. If the answer is yes (which is where I'm having the problem), the file from which the macro is being run is qfFile = ThisWorkbook.Name. So the file should be open, too. You see the first time the macro runs, it saves the quote to a new file and the macro is still in the file. If the customer wants to bring up an existing quote, modify it and reprocess it (run the macro again), my macro needs to know what the new quote filename is to process further. Thanks for trying, but any other ideas would be great. Michele |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Script out of range error
Hi,
Yes, you're right, both files are the same now. I had it that way for something that's not in the program anymore. So I've just put 'qrFile = ThisWorkbook.Name' after the If Else. However, I still can't get the macro to work. It's now stopping with 'Path not found' on the 'Error errnum' (third last line) in the IsFileOpen module which it never did before. At this time, it's not open. Here's the module and the code. I didn't include this part of the code in the code above before as I took it out for simplicity, but it was there. -----------Here is the IsFileOpen module Function IsFileOpen(FileName As String) Dim filenum As Integer, errnum As Integer On Error Resume Next ' Turn error checking off. filenum = FreeFile() ' Get a free file number. ' Attempt to open the file and lock it. Open FileName For Input Lock Read As #filenum Close filenum ' Close the file. errnum = Err ' Save the error number that occurred. On Error GoTo 0 ' Turn error checking back on. ' Check to see which error occurred. Select Case errnum ' No error occurred. ' File is NOT already open by another user. Case 0 IsFileOpen = False ' Error number for "Permission Denied." ' File is already opened by another user. Case 70 IsFileOpen = True ' Another error occurred. Case Else Error errnum End Select End Function ------------Here is the macro Sub ProcessCS() 'Macro6 Macro Const qfPath = "C:\Documents and Settings\John\My Documents\quoteprogramfiles\" Const qrPath = "C:\Documents and Settings\John\My Documents\CSQuotes\" ' Check if you are in the quote or a processed quote If isFile(qfPath & "CSQuoteForm.xls") = False Then response = MsgBox("This quote has already been processed. Do you want to create a new quote with a new quote number by copying this already processed quote?", _ vbYesNo + vbQuestion) Exit Sub Else response = vbNo End If qfFile = ThisWorkbook.Name ' Quit if quote report is open and open if not If IsFileOpen(qrPath & qrFile) = True Then MsgBox "Quote report is open. Save and close " & qrFile & " and try again." Exit Sub Else Workbooks.Open qrPath & qrFile End If ' Get last quote# and paste next quote# in report Range("A1").Select Selection.End(xlDown).Select Dim z As Integer q = ActiveCell.Value + 1 Selection.Offset(1, 0).Select ActiveCell = q ' Paste next quote# in quote Windows(qfFile).Activate Range("F3").Select ActiveCell = q I would really appreciate some help on this. I hope it's not me being stupid. It's probably my If Else End stuff because I'm not very good at that. Thank you, Michele |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Script out of range error
You used to have a constant defined
Const qrFile = "CSQuoteReport.xls" it is missing in your current code. So when you do If IsFileOpen(qrPath & qrFile) = True Then You aren't passing in a filename, just the path. -- Regards, Tom Ogilvy wrote in message ups.com... Hi, Yes, you're right, both files are the same now. I had it that way for something that's not in the program anymore. So I've just put 'qrFile = ThisWorkbook.Name' after the If Else. However, I still can't get the macro to work. It's now stopping with 'Path not found' on the 'Error errnum' (third last line) in the IsFileOpen module which it never did before. At this time, it's not open. Here's the module and the code. I didn't include this part of the code in the code above before as I took it out for simplicity, but it was there. -----------Here is the IsFileOpen module Function IsFileOpen(FileName As String) Dim filenum As Integer, errnum As Integer On Error Resume Next ' Turn error checking off. filenum = FreeFile() ' Get a free file number. ' Attempt to open the file and lock it. Open FileName For Input Lock Read As #filenum Close filenum ' Close the file. errnum = Err ' Save the error number that occurred. On Error GoTo 0 ' Turn error checking back on. ' Check to see which error occurred. Select Case errnum ' No error occurred. ' File is NOT already open by another user. Case 0 IsFileOpen = False ' Error number for "Permission Denied." ' File is already opened by another user. Case 70 IsFileOpen = True ' Another error occurred. Case Else Error errnum End Select End Function ------------Here is the macro Sub ProcessCS() 'Macro6 Macro Const qfPath = "C:\Documents and Settings\John\My Documents\quoteprogramfiles\" Const qrPath = "C:\Documents and Settings\John\My Documents\CSQuotes\" ' Check if you are in the quote or a processed quote If isFile(qfPath & "CSQuoteForm.xls") = False Then response = MsgBox("This quote has already been processed. Do you want to create a new quote with a new quote number by copying this already processed quote?", _ vbYesNo + vbQuestion) Exit Sub Else response = vbNo End If qfFile = ThisWorkbook.Name ' Quit if quote report is open and open if not If IsFileOpen(qrPath & qrFile) = True Then MsgBox "Quote report is open. Save and close " & qrFile & " and try again." Exit Sub Else Workbooks.Open qrPath & qrFile End If ' Get last quote# and paste next quote# in report Range("A1").Select Selection.End(xlDown).Select Dim z As Integer q = ActiveCell.Value + 1 Selection.Offset(1, 0).Select ActiveCell = q ' Paste next quote# in quote Windows(qfFile).Activate Range("F3").Select ActiveCell = q I would really appreciate some help on this. I hope it's not me being stupid. It's probably my If Else End stuff because I'm not very good at that. Thank you, Michele |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB script error | Excel Discussion (Misc queries) | |||
VB Script error | Excel Discussion (Misc queries) | |||
Macro script error - pls help !! | Excel Discussion (Misc queries) | |||
Script Error | Excel Discussion (Misc queries) | |||
Run Time Error 9, Script Out of Range | Excel Programming |