How to run a macro from one excel file to another?

[cogivina]

Hi,

I have MyFirstFile.xls open and I'd like to execute the Marco "Update_Data" in the module "mUpdate" from the MySecondFile.xls. I get "Application-defined or object-defined error" at the .Application.Run "Update_Data".

Set MyXL = GetObject("MySecondFile.xls")
With MyXL
.Application.Visible = True
.Application.DisplayAlerts = False
.Application.Run "Update_Data"
.Save
.Close
End With

Thanks for your helps.

 

[RonRepp]

Try this:

Application.Run "WorkbookName.XLS!Update_Data"

'NOTE: If WorkbookName has any spaces in it, you MUST use this syntax:

Application.Run "'Workbook Name.XLS'!Update_Data"

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[cogivina]

Thanks, Ron.

It works. The problem now is when I try to open MySecondFile.xls. All it shows is just an empty excel workbook. If I go into the VBA, I could see all the modules and objects from MySecondFile.xls. What did it cause this to happen? How can I fix it?
-----------------------------------------------
Set MyXL = GetObject("MySecondFile.xls")

With MyXL
.Application.Visible = True
.Application.DisplayAlerts = False
.Application.Run "MySecondFile.xls!Update_Data"
.Application.DisplayAlerts = True
.Save
.Close
End With
--------------------------------------------------

 

[RonRepp]

IMHO, I would use the Excel Application over late binding.

Workbooks.Open("MySecondFile.xls")
Application.DisplayAlerts = False
Application.Run "MySecondFile.xls!Update_Data"
Application.DisplayAlerts = True
''ThisWorkbook.Activate --Use this if the code is in the workbook you want to save instead of the following line:
Application.Windows("MyFileToSave").Activate
Activeworkbook.Save
Activeworkbook.Close Savechanges:=False

Why it is coming up blank is beyond me. My guess is that during testing, you overwrote the worksheet with data.

If the workbook opens and runs your macro, it's not your code that's wrong.

I hope this helps.





Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.