Subscript out or range Excel

[alwayslrN]

Using Excel 2002 I have a macro which copies data from one spreadsheet, opens another spreadsheet, copies the records to the spreadsheet that was opened, and this closes that spreadsheet after the paste.

It is possible for the user to already have that spreadsheet open. I have found code that is able to verify the spreadsheet is already open. What I want to do is, if the spreadsheet is already open run my procedure that pastes the records and closes the spreadsheet, but if the spreadsheet is not already open, have the macro do what it does now, open the spreadsheet, paste the records, then close the spreadsheet.

The later is working fine. My problem is with the former. I get a message stating subscript out of range. I suppose that means I am referencing the workbook/spreadsheet incorrectly, but I am not sure how to fix it.

     ServicerFolder = wsMainMenu.Range("G5").Value
     RemittanceMonth = Left(wsMainMenu.Range("G4").Value, 2)
     RemittanceYear = Mid(wsMainMenu.Range("G4").Value, 3, 4)
     
  
     ChDir ServicerFolder
     
     If IsFileOpen(ServicerFolder & "\monthlyremittance.xls") Then
        [b]Windows(ServicerFolder & monthlyremittance.xls).Activate[/b]

    Else
        Workbooks.Open filename:=ServicerFolder & "\monthlyremittance.xls"

    End If

Thanks

 

[SkipVought]

Hi,

Fro some reason, I hate using the Windows collection...

     If IsFileOpen(ServicerFolder & "\monthlyremittance.xls") Then
        [b]Workbooks[/b](ServicerFolder & monthlyremittance.xls).Activate

    Else
        Workbooks.Open filename:=ServicerFolder & "\monthlyremittance.xls"

    End If

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[PHV]

what about this ?
Windows("monthlyremittance.xls").Activate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

Thanks Skip and PH, but for some reason neither of those worked. I actually tried PH's suggestion yesterday and tried it again today just make sure I wasn't losing it.

Question - when doing my testing I have the monthlyremittance.xls workbook open in a different Excel instance (i.e. I have two open Excel sessions) than where the macro is running. Does that make a difference? I am trying to be proactive in what I know the user will do.

 

[alwayslrN]

Just did a little test to answer my own question above. PH's suggestion works as long as I am in the same instance of Excel. If I have Excel open, then open another instance and in that instance have the monthlyremittance.xls workbook open, PH's suggestion does not work.

I tried using Skips in both scenarios and I get the subscript out of range issue.

 

[Fr33dan]

Just a stab in the dark but try this:

    If IsFileOpen(ServicerFolder & "\monthlyremittance.xls") Then
        On Error Resume Next
        Workbooks("monthlyremittance.xls").Activate
        Windows("monthlyremittance.xls").Activate
        On Error GoTo 0
    Else
        Workbooks.Open FileName:=ServicerFolder & "\monthlyremittance.xls"

    End If

 

[PHV]

What about this ?
Set myWB = GetObject(ServicerFolder & "\monthlyremittance.xls")
myWB.Activate

If the spreadsheet is already open (in ANY instance of Excel) then it is not opened again ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[alwayslrN]

Thanks PH - I removed the if then else and replaced it with what you provided. Now I have a new issue. The following code stops leaves the procedure once it gets to the line in bold.

    Worksheets(1).Activate
    MLastRow = Cells(Cells.Rows.Count, "a").End(xlUp).Offset(1).Row
    Mlast = MLastRow
    Range("a" & MLastRow).Select
    
'    Range("A2").Select
    [b]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False[/b]
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Selection.ClearContents

 

[PHV]

You have to do the job with the myWB object ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886