Worksheets and Windows

[prefix]

Ok i've run out of things to try, i've tried everything in order to reference / activate another workbook from an existing one, it just gives me subscript out of range (cant find the worksheet?)

I am pretty certain the name is right, and i've tried these codes;

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Always gives me subscript out of range for Workbooks and Windows.. Why? What am i doing wrong :X I've tried to copy data out of the workbook and into another and i've tried to reference the Open Workbook directly to get the data, nothing works, HELP

[phildack]

If the other workbook is already open, all you need is workbooks("autograf").activate - using the .xls is what probably messed it up.

The other thing is just to double check that you've spelled it all correctly! Your Windows.activate code should work fine IF you have a workbook called Autograf and IF it is already open.

Phil

[prefix]

I already tried that, but thanks for your answer

I'm now down to trying to just copy the data into the active workbook by using this:

Please Login or Register  to view this content.

But it still gives subscript out of range on Windows("Autograf").Activate
both with and without the .xls, wierd huh?

[phildack]

I'm pretty sure the Windows object needs the full filename, with the .xls. If that still isn't working then yes, that does seem a bit odd! I'm not sure if Workbooks is case sensitive, could that be it? All I can suggest is trying this:

Please Login or Register  to view this content.

Have you tried just recording a macro to do what you want? That may be the simplest and easiest way to fix this!

[teylyn]

phildack, please stop quoting whole posts. It's just clutter.

thanks

[prefix]

Run time error '9' subscript out of range with the code;

Please Login or Register  to view this content.

*scratches head*

Altso tried Workbooks("Autograf.xls").Worksheets("Legg inn").Activate
and Windows("Autograf").Activate

No go. I find this very very strange, as i thought these were two very common functions in Excel, it must be something very simple i'm doing wrong.

Once i get this code working i plan to insert it into my existing code in order to have a chart created in another Workbook, or get the data from another workbook and create the chart in the active one. But neither of these methods seem to work with this;

Please Login or Register  to view this content.

The clues here are;

Please Login or Register  to view this content.

And Sheets("Graf") should be able to be changed to add the chart to another workbook instead right? Or if that doesnt work i should be ablet o set the rngData range to the sheet containing the data in the other workbook from the other workbook. If that makes sense :p


Even recording the macro and trying to execute it fails;

Please Login or Register  to view this content.

[romperstomper]

Is your workbook hidden? (you can't activate a hidden window)
You should be able to use Workbooks("Autograf.xls") assuming the name is correct, and note that it is safer to include the file extension in the name.

[phildack]

phildack, please stop quoting whole posts. It's just clutter.

thanks

Noted. Lengthy quotes are clutter. Public dressing downs apparently not.

[prefix]

@ Romperstomper: I recently created this workbook so i don't see how it can be hidden :D However i did check that just to be sure and none of the Workbooks are hidden.
I've attached both workbooks so you can try this for yourself. Frankly, i'm stumped.

[romperstomper]

What are the steps you take to produce the error? (I do get an error but that's due to references to Chart 64 which doesn't exist anywhere that I can see)

[prefix]

Oh sorry :D Thought i had the chart already made but just run the macro Makecharts and the Sheet Graf should be populated with a chart, then simply load up graf.xls and try to run WorkNOW. (That's when i get the subscript out of range) You might have to change the name of the Chart in the Graf sheet(Or change the name in the WorkNOW macro), if it receives another name.

[romperstomper]

Your WorkNOW() macro does not refer to a chart, but it can be simplified to this:

Please Login or Register  to view this content.

Also, your routines should not be in the ThisWorkbook modules of your workbooks - they should be in normal modules.

[prefix]

Ugh. So you're saying i've been doing that wrong for all this time :D I shouldn't write code in ThisWorkbook but rather for each sheet? :D I'll try this code in the sheet in question hope it works :D

edit: it did work, without a hitch, how do i feel you ask? Like a noob :D Anyway, thanks for pointing this out for me :D By the way, is it only these commands (Windows,Workbooks) that are affected by ThisWorkbook?

Thank you for your answers! :D

[romperstomper]

Ugh. So you're saying i've been doing that wrong for all this time :D I shouldn't write code in ThisWorkbook but rather for each sheet?

No, I'm saying that the code should be in normal modules, not worksheet or ThisWorkbook modules. You can add a new normal module via the Insert-Module option in the VB Editor window. (they are named Module1, Module2 etc. by default)

By the way, is it only these commands (Windows,Workbooks) that are affected by ThisWorkbook?

In the ThisWorkbook module, an unqualified reference to Windows will refer to the windows of the workbook containing the code (you can have multiple windows open on one workbook) because Windows is a property of the Workbook object. What you need to do is specify that the Windows property refers to the Application object in order to access all open workbooks or, as here, move your code to a normal module. Normal modules do not have properties like Windows so any unqualified reference to Windows will actually default to Application.Windows

As an example, if you have two workbooks open (Book1 and Book2) and you put this in the ThisWorkbook module of Book1:

Please Login or Register  to view this content.

you will get a 'Subscript out of range' error on the first statement, but the second will work fine. If, however, you move the code to a normal module, both lines will work because they both refer to Application.Windows - it is hopefully obvious that it is safer to fully qualify all objects!
There are similar issues with code in Worksheet code modules where any references to Cells or Range that are not explicitly qualified with a worksheet object, will refer to the sheet containing the code and not the active worksheet, as is the case if the code is in a normal module.

Does that make sense?

[prefix]

Yes, it does! Thank you for that information, i learned alot from it, i will be using modules as much as possible from now on. I also realized that you should define as much as possible or you will get errors :P I have learned this from hands on experience, just didnt realize what the issue was until now