error when running macro from another workbook

  • Thread starter Thread starter Scooter
  • Start date Start date
S

Scooter

With the below code in a workbook I get "Cannot run macro. Macro may not be
available in this workbook or all macros my be disabled." I have checked
that all macros are enabled under the trust center and MonthlyUpdate is not a
private sub and does exist in the second workbook. What am I missing?


Sub UpdateBig()

Dim PathToFile As String
Dim File2Update As Workbook
Dim MyResult As Variant
Dim NameofFile As String

NameofFile = "abc.xls"
PathToFile = "C:\Documents and Settings\me\My Documents"
Set File2Update = Workbooks.Open(PathToFile & "\" & NameofFile)
MyResult = Application.Run(File2Update.Name & "!MonthlyUpdate")
Windows(File2Update).Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub
 
it may not help but i changed your code

check the excel security levels

Sub UpdateBig()

Dim PathToFile As String
Dim File2Update As Workbook
Dim MyResult As Variant
Dim NameofFile As String

NameofFile = "abc.xls"
PathToFile = "C:\Documents and Settings\me\My Documents"
Set File2Update = Workbooks.Open(PathToFile & "\" & NameofFile)
MyResult = Application.Run(File2Update.Name & "!MonthlyUpdate")

File2Update.Save
File2Update.Close

End Sub
 
actually, i didn't change your code where it matters :(
it looks fine & should work...hence the comment on security levels
 
The code is erroring on the line - MyResult =
Application.Run(File2Update.Name & "!MonthlyUpdate"). I think your changes
were after that.

The security setting is - Enable all macros (not recommended,
potentially dangerous code can run)
 
excel 2003?

Scooter said:
The code is erroring on the line - MyResult =
Application.Run(File2Update.Name & "!MonthlyUpdate"). I think your
changes
were after that.

The security setting is - Enable all macros (not recommended,
potentially dangerous code can run)
Click to expand...
 
Hi,
when opening the file you have to say yes to that message, better if you go
and save as the file and then choose extension xlsm (enable macros)
 
Back
Top