' Excel VBA copy using .Workbooks or .Windows OOP Method

[DocAElstein]

'
''. Hello Everyone … ---o00o---`(_)`---o00o---
''.
''. I am new to Excel VBA and I am trying to understand the theory better.
''.
''. I wanted a simple direct method to copy the content (in this case a number) from a cell in one Workbook (Mappe2)
'' into a cell in another Workbook (Mappe1). I wanted to write the code myself from an understanding of VBA OOP
'' rather than simply using the Macro Recorder
''.
''. That is (or should be) very simple and indeed I manage it. However, not all the Methods I tried worked and I do not understand why! Probably it is because I do not quite fully understand the theory or implementation of the Object/Class Orienteer Program Concept!
'. (Or is it the case that you can only take or place values in a cell in a Workbook that is not Activated,
'' using the unique .Windows.ActiveCell Method? (Note here that you sometime need to activate the receiving Workbook, (Mappe1)
‘. but not the Workbook with the desired value in it (Mappe2) )
''. And you can not directly work on a Cell by co-ordinates at all if the Workbook is not activated?
'.
'. Maybe a Profi can help me to understand the following Codes.:-
'
''. The following 4 Codes are an attempt by me on a simple direct Method.
''. But only the forth seems to work
''.
''‚…………… ……….---o00o---`(_)`---o00o---



''.------ 1
Sub WorkbooksCellsMethodDirect()
Excel.Application.Workbooks("Mappe1.xlsm").Application.Cells(1, 1).Range("a1").Value _
= Excel.Application.Workbooks("Mappe2.xlsm").Application.Cells(1, 2).Range("a1").Value
End Sub
'.


'. 2 ------------------------------------------------------------2
'.
Sub WorkbooksActiveCellMethodDirect()
Excel.Application.Workbooks("Mappe1.xlsm").Application.ActiveCell.Range("a1").Value _
= Excel.Application.Workbooks("Mappe2.xlsm").Application.ActiveCell.Range("a1").Value
End Sub
'.


'. 3 ------------------------ 3
'.
Sub WindowsCellsMethodDirect()
Excel.Application.Windows("Mappe1.xlsm").Application.Cells(1, 1).Range("a1").Value _
= Excel.Application.Windows("Mappe2.xlsm").Application.Cells(1, 2).Range("a1").Value
End Sub
'.
'. 4 ---------------------------------------4
'.
Sub WindowsActiveCellMethodDirect()
Excel.Application.Windows("Mappe1.xlsm").Activate
Excel.Application.Windows("Mappe1.xlsm").ActiveCell.Range("a1").Value _
= Excel.Application.Windows("Mappe2.xlsm").ActiveCell.Range("a1").Value
End Sub
'.
'. ----------------------------------------
'.
'. It looks as though only The Windows Object has an extra direct .ActiveCell under object
'. instead of only Application.Activecell available. Somehow this makes the difference!?!
'.
' (In other words:- Workbooks has .Application.Cells and .Application.ActiveCell.
'. Windows has also this .Application.Cells and .Application.ActiveCell,
'. ..but Windows has an extra .ActiveCell that Workbooks does not have!?!)
'.
'. Can anyone explain this to me!!?!
'.
' Thanks Alan Elston.
'. Hof, Bavaria on 23.05.2014
'. Email DocAElstein@t-online.de
'.............
'.
'. P.s. A more complicated method which activates every time the Workbook
‘. and stores the wanted cell value in a TemporaryVariable works every time!?
'. That is to say, the following 4 Methods all work fine!!!
'. ---o00o---`(_)`---o00o---

'.-----------
Sub WorkbooksCellsMethodWithTemporaryVariable()
Dim TemporaryVariable As Double

Excel.Application.Workbooks("Mappe2.xlsm").Activate

Let TemporaryVariable _
= Excel.Application.Workbooks("Mappe2.xlsm").Application.Cells(1, 2).Range("a1").Value

Excel.Application.Workbooks("Mappe1.xlsm").Activate

Excel.Application.Workbooks("Mappe1.xlsm").Application.Cells(1, 1).Range("a1").Value _
= TemporaryVariable
End Sub
'.
'. ------------------------------------------- '.

Sub WorkbooksActiveCellMethodWithTemporaryVariable()
Dim TemporaryVariable As Double

Excel.Application.Workbooks("Mappe2.xlsm").Activate

Let TemporaryVariable _
= Excel.Application.Workbooks("Mappe2.xlsm").Application.ActiveCell.Range("a1").Value

Excel.Application.Workbooks("Mappe1.xlsm").Activate

Excel.Application.Workbooks("Mappe1.xlsm").Application.ActiveCell.Range("a1").Value _
= TemporaryVariable
End Sub
'.
'. ----------------------------------------
'.
Sub WindowsCellsMethodWithTemporaryVariable()
Dim TemporaryVariable As Double

Excel.Application.Windows("Mappe2.xlsm").Activate

Let TemporaryVariable _
= Excel.Application.Windows("Mappe2.xlsm").Application.Cells(1, 2).Range("a1").Value

Excel.Application.Windows("Mappe1.xlsm").Activate

Excel.Application.Windows("Mappe1.xlsm").Application.Cells(1, 1).Range("a1").Value _
= TemporaryVariable
End Sub
'.
'. -------------------------------------------
'.
Sub WindowsActiveCellMethodWithTemporaryVariable()
Dim TemporaryVariable As Double

Excel.Application.Windows("Mappe2.xlsm").Activate

Let TemporaryVariable _
= Excel.Application.Windows("Mappe2.xlsm").Application.ActiveCell.Range("a1").Value

Excel.Application.Windows("Mappe1.xlsm").Activate

Excel.Application.Windows("Mappe1.xlsm").Application.ActiveCell.Range("a1").Value _
= TemporaryVariable
End Sub
'. -------------------------------------------------

 

[Andrew Poulsom]

You haven't specified a Worksheet in any of your examples. To copy the value in A1 on Sheet1 in Workbook Mappe2.xlsm to A1 on Sheet1 in Workbook Mappe1.xlsm:

Workbooks("Mappe1.xlsm").Worksheets("Sheet1").Range("A1").Value = Workbooks("Mappe2.xlsm").Worksheets("Sheet1").Range("A1").Value

 

[DocAElstein]

An/ To :- Andrew Pulson
Von/From :- Alan Elston
.. I Couldn't seem to reply to your answer.. I Probably don't understand this Site jetzt...Maybe it will work here..
....
.........................

' Great, that was very Helpful:-
' Most of the Code Methods below work, and I think I understand why.
' (I do not quite jet understand why method 5b and 7b below don't work OR
' why my original method 4 does work,
' but I guess that will come with time when I get more experience!!!!)
'
' Danke, Thanks
' Alan Elston
' Hof, Germany
' Sunday, 25.05.2014
'
' (P.s. I am using Excel 2007 and 2010 German version)
'
'
' ......................................
'‚. . … ---o00o---`(_)`---o00o--- ………..


''.------1b
Sub WorkbooksWorksheetsAndrewPoulsomCellsMethodDirect()
Excel.Application.Workbooks("Mappe1.xlsm").Worksheets.Item(1).Application.Cells(1, 1).Range("a1").Value _
= Excel.Application.Workbooks("Mappe2.xlsm").Worksheets.Item(1).Application.Cells(1, 2).Range("a1").Value
End Sub
'.
'. ------------2b
Sub AndrewPoulsom2()
Workbooks("Mappe1.xlsm").Worksheets("Tabelle1").Range("A1").Value _
= Workbooks("Mappe2.xlsm").Worksheets("Tabelle1").Range("B1").Value
End Sub
'.
'. ---------------------3b
Sub AndrewPoulsom3()
Workbooks("Mappe1.xlsm").Worksheets.Item(1).Range("A1").Value _
= Workbooks("Mappe2.xlsm").Worksheets.Item(1).Range("B1").Value
End Sub
'.
'.-----------------------------4b
Sub AndrewPoulsom4()
Workbooks("Mappe1.xlsm").Worksheets.Item(1).Cells(1, 1).Range("A1").Value _
= Workbooks("Mappe2.xlsm").Worksheets.Item(1).Cells(1, 2).Range("A1").Value
End Sub
'.
'.-----------------------------------5b
Sub AndrewPoulsom5()
Workbooks("Mappe1.xlsm").Worksheets.Item(1).Application.Cells(1, 1).Range("A1").Value _
= Workbooks("Mappe2.xlsm").Worksheets.Item(1).Application.Cells(1, 2).Range("A1").Value
End Sub
'
'.--------------------------------------------6b
Sub WorkbooksCellsMethodDirectAlanElstonAndrewPoulson()
Excel.Application.Workbooks("Mappe1.xlsm").Worksheets("Tabelle1").Cells(1, 1).Range("a1").Value _
= Excel.Application.Workbooks("Mappe2.xlsm").Worksheets("Tabelle1").Cells(1, 2).Range("a1").Value
End Sub
'.--------------------------------------------------7b
'.
Sub WorkbooksActiveCellMethodDirectAlanElstonAndrewPoulson()
Excel.Application.Workbooks("Mappe1.xlsm").Worksheets("Tabelle1").Application.ActiveCell.Range("a1").Value _
= Excel.Application.Workbooks("Mappe2.xlsm").Worksheets("Tabelle1").Application.ActiveCell.Range("a1").Value
End Sub
'.
'. -----------------------4 (Original)
'.
Sub WindowsActiveCellMethodDirekt4()
Excel.Application.Windows("Mappe1.xlsm").Activate
Excel.Application.Windows("Mappe1.xlsm").ActiveCell.Range("a1").Value _
= Excel.Application.Windows("Mappe2.xlsm").ActiveCell.Range("a1").Value
End Sub


'. ----------------------------------------

'-------------AndrewPoulsonInfo #####
' Re: ' Excel VBA copy using .Workbooks or .Windows OOP Method
' You haven 't specified a Worksheet in any of your examples. To copy the value in A1 on Sheet1 in Workbook Mappe2.xlsm to A1 on Sheet1 in Workbook Mappe1.xlsm:
' Workbooks("Mappe1.xlsm").Worksheets("Sheet1").Range("A1").Value = Workbooks("Mappe2.xlsm").Worksheets("Sheet1").Range("A1").Value
'---------------- ###################

 

[DocAElstein]

Oops!! , I made a small mistake:- 1b should look like this below and not as in my last reply...

' ......................................
'‚. . … ---o00o---`(_)`---o00o--- ………..


''.------1b
Sub WorkbooksWorksheetsAndrewPoulsomCellsMethodDirect()
Excel.Application.Workbooks("Mappe1.xlsm").Worksheets.Item(1).Cells(1, 1).Range("a1").Value _
= Excel.Application.Workbooks("Mappe2.xlsm").Worksheets.Item(1).Cells(1, 2).Range("a1").Value
End Sub
'.

' I think that's everything, now!
' Auf weidersehen, Goodbye
' Alan Elston

 

[Andrew Poulsom]

This:

Excel.Application.Workbooks("Mappe1.xlsm").Worksheets("Tabelle1").Application.ActiveCell.Range("a1").Value

is the same as:

Application.ActiveCell.Value

because the Application property returns an Application object.

 

[DocAElstein]

Re: ' Excel VBA copy using .Workbooks or .Windows OOP Method​

' To:- Andrew Poulson
' From:- Alan Elston
'
' Hi, Thanks again, great that you answered again.
' I do not quite understand if the 2 words .Application in the Code:
'
Excel.Application.Workbooks("Mappe1.xlsm").Worksheets("Tabelle1").Application.ActiveCell.Range("a1").Value
'
' mean the same thing, as I thought I am working down the OOP Hierarchy and so
' coming across different under Objects that coincidentally have the same name?
' Otherwise the code is allowing me to go back and forth in the hierarchy OOP system,
' which seems a bit strange. (Or does OOP allow this????? : I note that the word
' Application comes up all over the place in the Excel library!.
'
' But if I am slowly getting the point:....
' In either the .Windows object or the .Workbooks object
' or the Worksheets object there is an object called Application
' This has under objects called ActiveCell and Cells
' This Active Cell or the Cells will refer only to that in the
' activated workbook, regardless of which workbook or worksheet
' that you write in the ( ).
' The Windows has an ActiveCell under object which
' is different in that it refers to the workbook or worksheet
' that you write in the ( ) .
' (For some reason however you sometimes must at least activate
' the receiving Workbook initially)
'
' I am happy that I can now achieve what I want to do, and I think I am beginning to
' understand why some of ma earlier codes ( --1 --2 --3 ) didn't work.
' I am an old Practical Physicist and not a programmer, but I must say it seem very
' unprofessional a) having code with the same name that is in fact a
' different '"Object" and b) the syntax allows for specifying particular
' Workbooks or worksheets when this information is irrelevant because it always
' will refer to the activated workbook and worksheet. It would be more professional
' to allow only Workbooks.Application.Activecell Workbooks.Application.Cells
' Windows.Aplication.Activecell and Windows.Application.Cells and not
' Workbooks( ).Application.Activecell Workbooks( ).Application.Cells
' Windows( ).Aplication.Activecell and Windows( ).Application.Cells
' because as I explained (and as may experiments have shown) what is written in
' the ( ) is irrelevant in this case!!

' Or I simply don't quite get the Point yet- I'm new to VBA!!!!
' Thanks again.
' Dr (Physics) Alan Elston ‹(•¿•)›
' Hof, Germany
'
'
'. .........................
''-------------AndrewPoulsonInfo2#####
''
''Re: ' Excel VBA copy using .Workbooks or .Windows OOP Method
'This:
'Code:
Excel.Application.Workbooks("Mappe1.xlsm").Worksheets("Tabelle1").Application.ActiveCell.Range("a1").Value
' is the same as:
'Code:
Application.ActiveCell.Value
' because the Application property returns an Application object.
''
'----------------###################

 

[Andrew Poulsom]

The Application property returns the Application object irrespective of the object that calls it. It effectively goes back to the top of the object hierarchy. The Parent property is another way of going up the hierarchy.

There is only one ActiveCell in the Application and a Worksheet doesn't have an ActiveCell property.

 

[DocAElstein]

'' An / To :- Andrew Poulson
'' Von / From :- Alan Elston
'' Hallo,
'' Thanks, that was a very useful bit of "Profi" Info.
'' I did a lot of background reading just recently and many Books
'' say or at least give the impression that by using the "dot" ,
'' . you always work down the hierarchy. I have even been
'' told that by apparently experienced users!!! Clearly that is
'' not the case. S that's where I started to go wrong with my thinking!
'' So, as you say, .Worksheets( ) ( and presumably also
'' .Workbooks( ) ) don't have an ActiveCell. Also .Workbooks
'' Doesn't have Cells. VBA Syntax allowed me to write code
'' in my first ---3 methods which gave the impression
'' I was achieving this, but in fact I was always going back up to the
' .Application ( in this case the application of Excel). By
'' "going back up", the in-between code I wrote which gave the
'' impression I was accessing a particular Cell in a particular
'' Workbook was ignored/ irrelevant.
'' By "going back up" I was then using the "unique" Object
'' .Application. (Application is a Class and Excel.Application
'' is a type of this Class??? .....as you said.. Application is " .....
'' The Parent property ")
'' The application of Excel has as you say, only one Active Cell
'' which is always that of the activated Worksheet of a workbook.
'' as you said "The Application property returns the Application
'' object irrespective of the object that calls it"
''
'' .Windows( ) object however, does have an Active cell property,
'' or rather as many Active cells as there are open Workbooks.
'' These can be for example referenced by name

Let a = Windows("Mappe1").ActiveCell
Let b = Windows("Mappe2").ActiveCell

' or in the order that the Workbooks were opened,

Let a = Windows.Item(1).ActiveCell
Let b = Windows.Item(2).ActiveCell

'' where Item 1 is always the last workbook opened, item 2 the one before, etc.
'
'' I think I'm slowly "getting it"
'
''Thanks again
''Alan Elston

 

[DocAElstein]

Hello, ‹(•¿•)›
Greg Truby had this further answered in the international Forum
... In German.....Aber, ... But also In English !
Link / Thread



http://www.mrexcel.com/forum/questi...durch-workbooks-oder-windows-oop-methode.html




Alan

 

[DocAElstein]

Hello
There is some new info to this Theme on the German forum just now ( In English and German!!) in the following link / Thread


http://www.mrexcel.com/forum/questi...durch-workbooks-oder-windows-oop-methode.html


And also a bit of relevant comment yesterday in the English Forum in the following Link / Thread

http://www.mrexcel.com/forum/genera...nce%85-o00o-%60-_-%60-o00o-oop-hierarchy.html

Alan