[SOLVED]:Opening workbooks and enabling user defined macros to run in those workbooks

It might be useful to see the calling code. Does it, for instance, turn off calculations to improve speed? Furthermore, you may wish to explicitly set Application.AutomationSecurity to low (msoautomationsecuritylow I think is the constant) just to see if that helps. If it does not, then macro enablement would not appear to be the issue.

@OnErrorGoto0, thanks for the tip on turning off calculation. Yes it was done only for the workbook the VBA is updating to, ie Workbook B. Here is a fragment of the code which is located in Module1 of Workbook A:

' Macro to update changed data to Changed Data Tracking workbook B
Sub MergeData()
Dim cmFile As String
Dim wwb As Workbook
Dim rwb As Workbook

' Disable screen updates to eliminate flashing
Application.ScreenUpdating = False

On Error GoTo errhandler

If IsWorkbookOpen(WORKBOOK_B) Then ' WORKBOOK_B is defined as global constant
' Since already opened, point wwb to opened workbook
Set wwb = Workbooks(WORKBOOK_B)
Else
' Open tracking workbook to write to
Set wwb = Workbooks.Open(TPATH) ' Full pathname for WORKBOOK_B defined as a global constant
End If
wwb.Application.Calculation = xlCalculationManual ' set formula in Tracking workbook to calculate manually
wwb.Application.CalculateBeforeSave = True

' Setup to look for .xlsm workbooks in cost changes directory
cmFile = Dir(CMPATH & "*.xlsm", vbNormal) ' CMPATH is cost changes directory defined as a global constant
If cmFile = "" Then
Res = MsgBox("No workbooks found in cost changes directory. Exiting macro.", vbCritical)
wwb.Close SaveChanges:=False
Exit Sub
End If
FailCount = 0

' Loop until no .xlsm workbooks to process
Do Until cmFile = ""
' Open each .xlsm workbook for processing with password
Set rwb = Workbooks.Open(CMPATH & cmFile, , , , "password123")

' Call procedure to read from .xlsm workbook & update tracking workbook B
Call CopyData(wwb, rwb, cmFile)

' Close each .xlsm workbook without saving
rwb.Close SaveChanges:=False

errhandler:
If Err.Number > 0 Then
Res = MsgBox("Error processing " & cmFile & ": Write down error message & inform macro administrator")
Err.Clear
End If

' Look for next .xlsm workbook
cmFile = Dir
Loop
wwb.Close SaveChanges:=True

Res = MsgBox("Completed processing")
Application.ScreenUpdating = True
End Sub

Thanks to the reply from OnErrorGoto0, I managed to resolve the issue.

I did some testing of the xlCalculationManual setting within the code. It appears that when the first workbook is set to xlCalculationManual, all subsequently opened workbooks inherit that setting even though the workbooks themselves may have been set to xlCalculationAutomatic.

To overcome the issue, I added the statement

rwb.Application.Calculation = xlCalculationManual

immediately after opening each .xlsm workbook with the workbook handler assigned to rwb.

This enabled the user defined macros within each opened .xlsm workbook to be calculated.

Typo in my last reply. The code fragment I added to resolve the issue is:

Please Login or Register  to view this content.