' 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
'. -------------------------------------------------

 

[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

Alan