View Full Version : Run-Time error '9'
vzachin
11-29-2006, 12:26 PM
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
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
:dunno
thanks
zach
malik641
11-29-2006, 12:35 PM
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...:dunno
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:
Windows("" + ReportName + " ").Activate <---This Is where it fails
Notice I used plus + rather than the ampersand &.
Don't know if that'll do it, but maybe...:)
vzachin
11-30-2006, 06:58 AM
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
11-30-2006, 07:06 AM
Hey Zach,
Shoot. I was hoping that might do it. :think:
.......what if you tried to explicitly call out Windows using Application before it:
Application.Windows("" & ReportName & " ").Activate
And the same goes for the line below:
Application.Windows("oldreport.xls").Activate
Again, long shot...but maybe :dunno
Hope it works :thumb
EDIT: Also, if there's supposed to be a single quote before ReportName, shouldn't it be:
Application.Windows("""" & ReportName & " ").Activate
Try that too.
CBrine
11-30-2006, 07:30 AM
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.
Application.Workbooks("" & ReportName & " ").Activate
HTH
Cal
Norie
11-30-2006, 11:07 AM
Have you tried using the xls extension after the report name?
vzachin
12-01-2006, 08:00 AM
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
Workbooks.Open Filename:= _
"http://xxxxx.xxxxx.com/xxxxx/cpcirs/xxxxx/" & ReportName & " "
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.
AppActivate ("http://xxxxx.xxxxx.com/xxxxx/xxxxx/xxxxx/" & ReportName & " - Microsoft Internet Explorer")
again, thanks for all the input
Norie
12-01-2006, 10:15 AM
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?
Set wbOpen = Workbooks.Open (Filename:= _
"http://xxxxx.xxxxx.com/xxxxx/cpcirs/xxxxx/" & ReportName & " ")
vzachin
12-01-2006, 11:15 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.