Mathematical Excel tool Automation RRS feed

  • Question

  • I have developed a calculator in "TOOL.xlsx" file. It takes 4 inputs and returns 2 outputs. The calculation is performed on the calculator sheet in the "Tool.xlsx" workbook. 4 inputs correspond to 1 data set. I have another Excel file named "DATA.xlsx" that contains around 20,000 datasets (4 inputs per data set) and it also has an output column that collects the output. I would like to automate the "DATA.slsx" and "Tool.xlsx" interaction so that the inputs for the "TOOL.xlsx" are automatically called from the "data.xlsx" and the output column in the "data.xlsx" are filled with the outputs of "TOOL.xlsx". I would really appreciate any help because I am really stuck up here. PS: I an new to VBA. Please find a screenshot ( )at that illustrates my problem.
    Saturday, March 14, 2015 6:53 PM


  • I recommend that you read this tutorial:

    Open the file TOOL.XLSX first, right-click the worksheet tab and select "View Code".

    Insert the code below into the editor and save the file as TOOL.XLSM, otherwise the macro is removed (XLSX files can not save macros).

    Open the file DATA.XLSX, press ALT-F8 and run the macro TOOL.XLSM!Test

    However, the execution will take some time, calculations performed in this way are very slowly.


    Sub Test()
      Dim R As Range
      Dim TInput(1 To 4) As Range
      Dim TOutput(1 To 2) As Range
      Dim i As Integer
      Dim Ws As Worksheet
      'Set a reference to the cells in tool.xlsx
      With Workbooks("Tool.xlsx").ActiveSheet
        For i = 1 To 4
          Set TInput(i) = .Range("C3").Offset(i - 1)
        For i = 1 To 2
          Set TOutput(i) = .Range("F3").Offset(i - 1)
      End With
      'Be sure the calculation is on
      Application.Calculation = xlCalculationAutomatic
      With Workbooks("Data.xlsx").ActiveSheet
        'Visit each cell in column B
        For Each R In .Range("B4", .Range("B" & Rows.Count).End(xlUp))
          'Copy the values from this row into tool.xlsx
          For i = 1 To 4
            TInput(i).Value = R.Offset(, i - 1).Value
          'Be sure the worksheet is calculated
          'Copy the results back into H:I
          For i = 1 To 2
            R.Offset(, 5 + i).Value = TOutput(i).Value
      End With
    End Sub

    Sunday, March 15, 2015 10:02 AM