0

I got 3 level Excel

A , B / C , B1 / B2 / C1 / C2

A/B/C already have a macro

A will collect data with B and C

B will collect data with B1 B2 and C collect C1 C2

if B and C didn't update the data A cannot get the latest data

how can I make that when I run a macro in A will auto update the information with A,B and C all excel

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Error404
  • 3
  • 2

2 Answers2

0

Your idea is such bad practice that Excel pointedly doesn't support it. This lack of support is demonstrated by the fact that the Worksheet_Change event isn't triggered by a change induced programmatically from an outside source.

Therefore you must rely on the capabilities of the macro in workbook A to make all the required changes in B and C. As mirror image: Don't try to trigger macros in workbooks B and C from a macro in workbook A.

However, it is possible for a macro in workbook A to call macros in workbooks B and C. The difference is in who retains control. So, your updating macro in workbook A could first update workbooks B and C and then call macros in those workbooks that process the updates.

The question to answer is what advantage can be derived from not having these macros all in workbook A. I point to the cost. Since the dependent workbooks are controlled independently (otherwise they wouldn't be separate) circumstances may interfere with access and intended updates may not happen. If the code were in workbook A problems can easily be reported. If the code resides in remote workbooks reporting of irregularities is much, much more complicated.

Variatus
  • 14,293
  • 2
  • 14
  • 30
  • I try to call the B macro at A but there is some error I think is the definition problem, but I can't fix it. The data was start from A2 to C as below – Error404 Jul 26 '21 at 08:38
  • As your "Answer" demonstrates, the question has shifted and became another one, perhaps how to call a macro from another workbook or access another workbook - I can't know which until the question put to me as a question, not as a problem. I suggest you identify the question, then research it (there are plenty of answers to either question here) and only then ask it, in another thread, if the result of your research necessitates such action. – Variatus Jul 26 '21 at 08:50
0

I try to call the B macro at A but there is some error I think is the definition problem, but I can't fix it. The data was start from A2 to C

Sub Collect ()
Dim awb As Workbook
Set awb = ThisWorkbook
Dim ActiveLastLine As Integer
Dim MyFiles As String

MyFiles = Dir("D:\B\*.xlsx")

Do While MyFiles <> ""
Workbooks.Open "D:\B\" & MyFiles
ActiveLastLine = awb.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
BLastLine = Cells(Rows.Count, 1).End(xlUp).Row

DataRange = Range("A2:C" & BLastLine)
awb.Worksheets("Sheet1").Range("A" & ActiveLastLine & ":C" & ActiveLastLine + BLastLine - 2) = DataRange

ActiveWorkbook.Close

MyFiles = Dir
Loop
End Sub

and i make a call at A file

Sub CallAnotherMacro()

For i = B to C

Application.Run "Path" & i & ".xlsm!Collect"

Next

End Sub

I'm sorry its quite confusion of my express

Error404
  • 3
  • 2
  • Please update your question instead of posting as an answer. `there is some error` is not a useful description, what exact error message did you receive? – Raymond Wu Jul 26 '21 at 08:47
  • Sorry , RunTime Error 1004 cannot run macro "Path" & i & ".xlsm!Collect". the macro may not be available in this workbook or all macro may be disabled – Error404 Jul 26 '21 at 09:01
  • https://stackoverflow.com/questions/25638344/programmatic-access-to-visual-basic-project-is-not-trusted, a quick google of the error message would have given plenty of possible solutions, if they still don't work then please post a new question (as it will be out of scope of your current question) – Raymond Wu Jul 26 '21 at 09:04