Run-Time error '9'

[vzachin]

hi,

I have a sub that works with Excel 2000 but failed when I gave it to a co-worker in another part of the country that is running Excel 97. I have no way of testing this in 97 because it's not available in my local office.
Any ideas?

This is the error message:
Run-Time error '9':
Subscript out of range

[vba]
Dim ReportName As String
ActiveWindow.Visible = True
Application.DisplayAlerts = False
Windows("" & ReportName & " ").Activate <---This is where it fails
Cells.Select
Selection.Copy
Windows("oldreport.xls").Activate
[/vba]



thanks
zach

[malik641]

Hi Zach,

What's in the ReportName string? And do you really need the quote (") in the before ReportName and the space (" ") after ReportName?

Just from the code alone it looks like you're activating a window with a space for the caption...



Putting that aside, I know nothing of Excel 97, and this may be a long shot, but if what you're using does work for Excel 2000 like you said, then try this:

[VBA]
Windows("" + ReportName + " ").Activate <---This Is where it fails
[/VBA]
Notice I used plus + rather than the ampersand &.

Don't know if that'll do it, but maybe...

[vzachin]

Hi Joseph,

Thanks for the reply. The error is still there with the + replacing the &. For some reason, the quote (") is required before the ReportName and space quote ( ") after the ReportName. I'm grabbing the ReportName from a mainframe and it shows the quotes. The file opens without any problems so the ReportName "seems" to be correct.
Back to the drawing board on this.

thanks for your thoughts
zach

[malik641]

Hey Zach,

Shoot. I was hoping that might do it.

.......what if you tried to explicitly call out Windows using Application before it:

[vba]
Application.Windows("" & ReportName & " ").Activate
[/vba]
And the same goes for the line below:
[vba]Application.Windows("oldreport.xls").Activate [/vba]
Again, long shot...but maybe

Hope it works

EDIT: Also, if there's supposed to be a single quote before ReportName, shouldn't it be:
[vba]
Application.Windows("""" & ReportName & " ").Activate
[/vba]
Try that too.

[CBrine]

I don't use the windows selection often, only when I don't have a choice. So forgive me if I'm wrong, but I'm pretty sure it selects workbooks? Why not try this.

[VBA]
Application.Workbooks("" & ReportName & " ").Activate
[/VBA]

HTH
Cal

[Norie]

Have you tried using the xls extension after the report name?

[vzachin]

Hi All,
Thanks for replying to my dilemma. I "think" i know where the problem is.
The report that I am getting is opened and viewed in Internet Explorer
[VBA]
Workbooks.Open Filename:= _
"http://xxxxx.xxxxx.com/xxxxx/cpcirs/xxxxx/" & ReportName & " "
[/VBA]
i used the appactivate method to select the window; now i'm trying to figure out how to select a cell in the window. hopefully that won't be too hard.
[VBA]
AppActivate ("http://xxxxx.xxxxx.com/xxxxx/xxxxx/xxxxx/" & ReportName & " - Microsoft Internet Explorer")
[/VBA]

again, thanks for all the input

[Norie]

Why don't you set a reference to the workbook when you open it?

PS why are you opening it in IE in the first place?
[vba]
Set wbOpen = Workbooks.Open (Filename:= _
"http://xxxxx.xxxxx.com/xxxxx/cpcirs/xxxxx/" & ReportName & " ")
[/vba]

[vzachin]

hi Norie,

I'll try your suggestion and get back to you.
The file i'm opening is on an intracompany website. the file is created from a query on a mainframe application using the user's input id as the filename output.
my idea was to have this code work for all users. the reportname is the string that i grab from the mainframe prior to the generation of the report. then i have to go to the website to retrieve the data.

zach