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

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

<michele@quality-computing.com> wrote in message
news:1116946140.275355.15170@g47g2000cwa.googlegroups.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
>

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

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

<michele@quality-computing.com> wrote in message
news:1116952044.718753.20650@g49g2000cwa.googlegroups.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
>

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

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




<michele@quality-computing.com> wrote in message
news:1116964951.857247.53080@g49g2000cwa.googlegroups.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
>