Misc. Leonardo1234 rider@1234 vixer. Highlighting. Simple Early stuff. Avinash Introduction

[DocAElstein]

From Here:
https://www.excelforum.com/excel-pro...dd-by-vba.html
https://www.excelforum.com/excel-pro...ml#post5177301
https://www.excelforum.com/excel-pro...ml#post5177506

---

See the attached files
Vba is placed in a "separate file"
Open 1.xls file
Calculate the data add all the data in column H and paste the result in K9 of vba contained file in sheet1
and close the 1.xls file

Hello,
Note:
_ I am assuming that the "separate file" is _ 9.xlsb _ , that is to say, the separate file with the VBA macro in is _ 9.xlsb
_ I assume that _ 9.xlsb _ and _ 1.xls _ are in the same folder, ( so I can use ThisWorkbook.Path to get the string path to the folder )


This I got from the macro recorder:

' From macro recorder:
Sub Makro1()
'
' Makro1 Makro
'

'   Open 1.xls file
    Workbooks.Open Filename:= _
        "F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\1.xls"
    Windows("9.xlsb").Activate
    
'   Type in a formula to get the sum  .....  add all the data in column H and paste the result in K9
    Range("K9").Select
    ActiveCell.FormulaR1C1 = "=SUM('[1.xls]1-Sheet1'!R2C8:R121C8)"
    Range("K10").Select
    Windows("1.xls").Activate
    
'   close the 1.xls file
    ActiveWindow.Close
End Sub
'




















' Closed workbook reference   =SUMME('F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\[1.xls]1-Sheet1'!$H$2:$H$121)
' Open workbook reference     =SUMME('[1.xls]1-Sheet1'!$H$2:$H$121)

This I wrote myself:

Sub Vixer3a() ' http://www.excelfox.com/forum/showthread.php/2353-add-by-vba?p=11421&viewfull=1#post11421
Rem 1 Workbook and worksheets info
'1a) Workbook info
Dim Wb1 As Workbook ' (will be set later when the workbook is opened)
Dim strWb1 As String: Let strWb1 = "1.xls"
Dim Wbm As Workbook: Set Wbm = ThisWorkbook ' I am assuming that this workbook is the seperate macro workbook
'1b) Worksheets info
Dim Ws1 As Worksheet ' (will be set later when the workbook is opened)
Dim Wsm As Worksheet
'Set Wsm = ThisWorkbook.Worksheets.Item(1) ' The first worksheet in this workbook ..
Set Wsm = ThisWorkbook.Worksheets("Sheet1") '     ..    use tab Name as alternative                                     http://www.excelfox.com/forum/showthread.php/2349-Find-percentage-by-vba?p=11400&viewfull=1#post11400
Dim Lr As Long: Let Lr = 121 ' For this example I am using 120 rows of data ( 2 - 121 )
Rem 2 Open  1.xls  file
'Workbooks.Open Filename:= "F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\1.xls"
'Workbooks.Open Filename:=ThisWorkbook.Path & "\" & "1.xls"         '  I assume that   9.xlsb   and   1.xls   are in the same folder, ( so I can use ThisWorkbook.Path to get the string path to the folder of  1.xls)    
 Workbooks.Open Filename:=ThisWorkbook.Path & "\" & strWb1
 Set Wb1 = ActiveWorkbook ' The workbook just opened will now be the current active workbook
 Set Ws1 = Wb1.Worksheets.Item(1)
Rem 3  add all the data in column H of  1.xls  and paste the result in K9 of vba contained file in sheet1
 'Wbm.Activate: Wsm.Range("K9").Select: Application.Calculation = xlCalculationAutomatic
' Let Wsm.Range("K9").Value = "=SUM('[1.xls]1-Sheet1'!$H$2:$H$121)"   '  Note:  In VBA we must write the formulas in English
' Let Wsm.Range("K9").Value = "=SUM('[" & strWb1 & "]1-Sheet1'!$H$2:$H$121)"
' Let Wsm.Range("K9").Value = "=SUM('[" & strWb1 & "]" & Ws1.Name & "'!$H$2:$H$121)"
 Let Wsm.Range("K9").Value = "=SUM('[" & strWb1 & "]" & Ws1.Name & "'!$H$2:$H$" & Lr & ")"
 Let Wsm.Range("K9").Value = Wsm.Range("K9").Value ' change cell content from formula to its value
Rem 4 close the 1.xls file
 Wb1.Close
End Sub































' Closed workbook reference   =SUMME('F:\Excel0202015Jan2016\ExcelFox\vixer\Von Vixer\[1.xls]1-Sheet1'!$H$2:$H$121)
' Open workbook reference     =SUMME('[1.xls]1-Sheet1'!$H$2:$H$121)



Sub Oops()
Application.Calculation = xlCalculationAutomatic
End Sub

Alan