CODEWITHSUNDEEP
excelvba
Jesse Smothermon
Jesse Smothermon

Reputation: 1051

Excel VBA Performing calculations using a separate workbook

I'm trying to write a bit of code that will collect information from an Excel workbook and then determine all of the calculations into a separate workbook. This includes creation of columns, generating the values, and creating a graph once the user clicks a button that I've already created. I can already perform these operations if the information is in the same workbook (and sheet) as the calculations. I am also able to select a specific sheet in another workbook. The question is, how do I combine these two bits of code to work?

Here's the code to open up a second workbook:

Dim file_path As String
Dim excel_title As String
Dim second_excel_title As String

file_path = "C:\Work\EXCEL_TEST\"
excel_title = "test_info"
second_excel_title = "test_calculation"

Set wbs = Workbooks
wbs.Open (file_path & excel_title)
Set wb = wbs.Item(excel_title)
wb.Sheets.Add after:=wb.Sheets(wb.Sheets.Count)

wb.Sheets("Sheet1").Select

As a test I was going to make a 3 by 3 block of numbers (1 - 9) in the test_info workbook and then perform random calculations on them (such as subtraction and multiplication) and output the answers in the test_calculation.

Thank you,

Jesse

Upvotes: 0

Views: 2269

Answers (2)

chris neilsen
chris neilsen

Reputation: 53135

Here's a sub based on your code with a few mods to demo referring to wb's and sheets, adding data and charts

Sub zx()
    Dim file_path As String
    Dim excel_title As String
    Dim second_excel_title As String
    Dim wb As Workbook
    Dim sh1excel As Worksheet, sh2excel As Worksheet
    Dim cht1 As Chart, cht2 As Chart
    Dim i As Long, j As Long

    Set sh1excel = ThisWorkbook.ActiveSheet
    file_path = "C:\Users\Chris\Documents\"
    excel_title = "test_info"
    second_excel_title = "test_calculation"

    Set wb = Workbooks.Open(file_path & excel_title)

    Set sh2excel = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    wb.Activate
    sh2excel.Activate

    ' Put some data in test_info at C3:D5
    For i = 0 To 2
    For j = 0 To 2
        sh2excel.Cells(i + 3, j + 3) = i * 3 + j + 1
    Next j, i

    ' Calculate from test_info into second_excel_title
    For i = 0 To 2
    For j = 0 To 2
        sh1excel.Cells(i + 3, j + 3) = sh2excel.Cells(i + 3, j + 3) ^ 2
    Next j, i

    ' or put it as a formula
    For i = 0 To 2
    For j = 0 To 2
        sh1excel.Cells(i + 3, j + 3) = "=[" & wb.Name & "]" & sh2excel.Name & "!" & sh2excel.Cells(i + 3, j + 3).Address & "^2"
    Next j, i

    ThisWorkbook.Activate
    'Add a chart into second_excel_title
    Set cht1 = ThisWorkbook.Charts.Add
    cht1.Activate
    cht1.Name = "DatafromThisBook"
    Do While cht1.SeriesCollection.Count > 0
        cht1.SeriesCollection.Item(1).Delete
    Loop
    cht1.SetSourceData Source:=sh1excel.Range("C3:E5")

    Set cht2 = ThisWorkbook.Charts.Add
    cht2.Activate
    cht2.Name = "DatafromOtherBook"
    Do While cht2.SeriesCollection.Count > 0
        cht2.SeriesCollection(1).Delete
    Loop
    cht2.SetSourceData Source:=sh2excel.Range("C3:E5")


End Sub

Upvotes: 1

Tiago Cardoso
Tiago Cardoso

Reputation: 2097

Add into the main workbook (that should not calculate data) the following code:

Private Sub Workbook_Open()
    Application.Calculation = xlManual
    Application.CalculateBeforeSave = False
End Sub

It will force Excel to do not calculate anything.

On the other hand, ensure that the 'calculation' workbook has the automatic option, i.e.:

Private Sub Workbook_Open()
    Application.Calculation = xlCalculationAutomatic
End Sub

Upvotes: 0

Related Questions