Solved: Activate vba in another workbook

[ukdane]

Excel 2003.

The setup.
I have a workbook, which successfully runs some code in the BEFORECLOSE.
This code opens another workbook, and copies details from the parent book to the child book.
It then saves the child workbook and closes it.
This all worked fine.

I then wanted to program some code into the child workbooks BEFORECLOSE, so that before closing the child workbook, it looked at some dates on the relevant sheets, and moved the rows around.
This in itself is also fine, and works.

However the idea is that when the parent workbook closes, it activates the childworkbook, runs the parentworkbook BEFORECLOSE, then runs the child workbook BEFORECLOSE, before saving and closing the child workbook, and ultimately the parent workbook. HOWEVER, the child workbooks BEFORECLOSE isn't activating.

The Question.
Is there anyway I can activate a macro in the childworkbook, directly from the parent workbook, so that I can bypass the child workbook's BEFORECLOSE?

[GTO]

Hi Ukdane,

Not sure if of help, but a simple example would be:

In a blank/new workbook saved as 'Child.xls', the code to run in Child would be placed in a Standard Module:

[vba]
Option Explicit

Sub EasyMac()
Dim wksSheet1 As Worksheet
Dim rngLastCellmodified As Range

Set wksSheet1 = ThisWorkbook.Worksheets("Sheet1")
Set rngLastCellmodified = wksSheet1.Cells(Rows.Count, 1).End(xlUp)

rngLastCellmodified.Offset(1).Value = rngLastCellmodified.Value + 1
End Sub
[/vba]

In another blank wb, saved in the same folder, and named Parent:

[vba]
Option Explicit

Sub exa()
Dim wbChild As Workbook

Set wbChild = Workbooks.Open(ThisWorkbook.Path & "\Child.xls")

Application.Run "Child.xls!Module1.EasyMac"

wbChild.Close True
End Sub
[/vba]

After setting a reference to the child wb and opening it, Application.Run runs the code in Child. To me leastwise, the strings can get a bit confusing if args are required.

Hope that helps,

Mark

[p45cal]

In general try, in all before_close event subs, to have only calls to other subs.
Now if you find that an event isn't being triggered for any reason, you can call those subs from anywhere, even using application.run to run a macro in a different workbook if necessary.

[ukdane]

Thanks guys, yes, the code in the BEFORECLOSE event subs called other sub code only.
The information I needed was the Application run..... I'll give it a go.
Thanks for your help.

Edit: I'm getting a run time error 1004- that the macro can't be found, but it's there, and it exists- I've checked spellings. Why won't it run/find the macro?

[VBA]Application.Run childname.Name & "!closedown.autoarchive"[/VBA]

The childname.Name is defined, I can see it when I step through the code.
I renamed Module1 to "closedown", and the sub IS called "autoarchive"

The workbook called childname is also open.

[mdmackillop]

If you need to pass arguments, add them as follows
[VBA]
Sub Test()
Application.Run "Child.xls!Module1.EasyMac", "Testing"
End Sub


Sub easymac(data As String)
MsgBox data
End Sub

[/VBA]

[ukdane]

I don't need it to pass arguments.
I just want it to run the macro (which doesn't run in the child's BEFORECLOSE sub)

The macro in question runs code that "archives" data that is too old. (It basically looks at a date on one worksheet, and if the date is older than todays date, then it moves the current row to the other worksheet, and removes it from the first worksheet.

This needs to be run everytime the workbook closes, then user doesn't need to do anything just close the initial parent workbook, and the code should run automatically.

Logically the solution provided above should work, but as I say, I'm getting a runtime error, as explained above.'


Edit: Problem solved, error in VBA code.
Code should read:
[VBA]Application.Run "'" & childname.Name & "'!closedown.autoarchive"[/VBA]

[mdmackillop]

Is AutoArchive a Private Sub? What value are you showing for Childname?

[GTO]

Edit: Problem solved, error in VBA code.
Code should read:
[vba]Application.Run "'" & childname.Name & "'!closedown.autoarchive"[/vba]

Hi Ukdane,

Sorry about that, I'll bet my bad. I'll bet there's a space in the actual name of Child.xls, like "My Child.xls" vs "MyChild.xls".

If that's correct, then yep, the single quotes need to surround the workbook name. I guess I should have said I have a hard time remembering the string construction regardless of args

Anyways, the single quotes surrounding seem to work whether the wb name has spaces in it or not, so maybe just safer to always include.

You'll have to let us know if that was it and glad you got it working

Mark

[ukdane]

Hi GTO,
Yup, you guessed it, there were spaces in the file name.
As you suggested, probably always a good idea to include the single quotation makrs, as that way, the code will always work

Thanks again for your help.

mdmackillop: Thanks mate, problem solved. childname was correctly showing the workbooks name. And no, the code wasn't in a private sub.