Opening Excel file and running macro

[gcservi]

Hi, all. I'm new to VBA but I've got one Excel file opening another and passing data, but can't get it to run a macro in the target file. The macro runs find when triggered with a button in the target file. What am I missing?

Source Excel macro

Sub Send_Info()
Dim dir As String
dir = ActiveWorkbook.Path
On Error Resume Next
Workbooks.Open Filename:=dir & "\macro test file 2.xlsm"
Workbooks("macro test file 1.xlsm").Activate
file = "macro test file 2.xlsm"
SheetData = "Sheet1"
Workbooks(file).Worksheets(SheetData).Range("A1") = Cells(ActiveCell.Row, 1)
Workbooks(file).Worksheets(SheetData).Range("B1") = Cells(ActiveCell.Row, 2)
Workbooks(file).Worksheets(SheetData).Range("C1") = Cells(ActiveCell.Row, 3)
Workbooks(file).Activate
Workbooks(file).Run "macro test file 2.xlsm!SaveToDesktop"
End Sub

Target Excel macro

Sub SaveToDesktop()
ThisFile = Range("B6").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
End Sub

[Kenneth Hobs]

Welcome to the forum! Please use VBA code tags.

Use as the first line of a Module:
Option Explicit
I don't see how that runs. Dir is a reserved keyword. Use something like sDir for a string variable.

There is no need to Activate a Workbook that you Open.

When using Cells, you probably want to prefix that with ThisWorkbook rather than leaving it as the default, the ActiveSheet in ActiveWorkbook.

Workbooks do not have a Run method. Use Application's Run method.

Public Sub Main()
Dim FunctionName As String, Result As Double
FunctionName = "Fun2" ' Selected function
Result = Application.Run("'" & ThisWorkbook.FullName & "'!Module1." & FunctionName)
MsgBox Result
End Sub

[gcservi]

Thanks, Kenneth. I got it cleaned up and it worked.

[gcservi]

From an open Excel file I'm trying to open a PPT template, update its links automatically, change the links to manual (to make the charts static), then save the PPT to a name in a cell in the source Excel file. So far I can open the file and update its links. Thanks.

Public Explicit Sub Run()
ThisFile = Range("B6").Value

Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True
objPPT.Presentations.Open "c:\PowerPoint template file.pptx"
objPPT.ActivePresentation.UpdateLinks
objPPT.LinkFormat.AutoUpdate = ppUpdateOptionManual
objPPT.Presentation.SaveAs Filename:=ThisFile

End Sub