PDA

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