pass the value from the open file to vba macro

[Megatronixs]

Hi all,

I found this code that is as some help to me:

Please Login or Register  to view this content.

What I need is to pass the value from NewFN to some part in the macro so I can refer to it.
I need to open a workbook and copy some data from it and then past it in another workbook.

Part of the solution was to use the Thisworkbook (as this is where the macro is) and ActiveWorkbook for the other workbook that was opened with the above code.
But if the user has more workbooks open, I would like to avoid that the wrong workbook could be selected.

Any ideas?

Greetings.

[tehneXus]

Hi,

you can use a variable to refer to a specific workbook to avoid "ActiveWorkbook" e.g.

Please Login or Register  to view this content.

[mrice]

Maybe use

Please Login or Register  to view this content.

[Megatronixs]

Hi tehneXus,

Thanks for the great help.
Is it also possible to do this with the sheet from the active workbook?
As the active workbook becomes xlWb, maybe this can also be done on the sheet, so if I would need data from the second sheet that could have a name "Data2".

This magic of Dim, Set and so on are still so difficult for me to understand. Are there any places/books where I could read and understand it better?

Thanks and greetings.

[tehneXus]

Is it also possible to do this with the sheet from the active workbook?

I'd suggest to always indicate the type of variable in the variable name, "Data2" can be everything. This will help you identifying the variable especially when the code gets longer and consist of much more variables. Also start your variable names with a lower case letter, upper case letters are used for functions and subs.
I am always using the "xl" prefix for excel objects and an abbreviation for the type, e.g. "Ws" = Worksheet and "Wb" = Workbook.
Also "sh" = Sheet is used a lot.

Please Login or Register  to view this content.

You can getting started with VBA with (While reading this please note, avoid using the "Select" function and the "Selection" object where possible):
Getting started: http://msdn.microsoft.com/en-us/libr...ice.14%29.aspx
A lot info: http://www.excelforum.com/excel-prog...materials.html
Very good page: http://www.cpearson.com/Excel/Topic.aspx