Run a macro from one excel file to another excel file

[CAM]

Hello,

I am using an Excel 2007 file containing several command buttons I call this
Excel file "AMProcessing". On one particular command button I want to run a
particular vba coding (marco) in the "AMProcessing" workbook that needs to
be used for another Excel file called "CCCardExpense". The vba coding will
do what it needs to do for "CCCardExpense" file. I don't want to put the
vba coding in the "CCCardExpense", because it is a download file "master".
How can I use one Excel file to run the vba coding (macro) for another
Excel file. I want to avoid puting a command button in the toolbar itself.
If I have to open "CCCardExpense" file that's fine, but I want to have it
close automaticlly after the macro is run. Any tips will be appreciated.
Thank you in advance.

Cheers,

 

[Gary Keramidas]

maybe something on the order of this as long as the files are in the same
folder. if not you need to change the location:

Sub test()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim wb As Workbook
Dim wb2 As Workbook
Dim fPath As String
Dim file2 As String
fPath = ThisWorkbook.Path & "\"
file2 = "CCCardExpense.xls"
Application.ScreenUpdating = False

Set ws = Worksheets("sheet1")
' test for CCCardExpense.xls
If Len(Dir(fPath & file2)) > 0 Then
Set wb2 = Workbooks.Open(fPath & file2, ReadOnly:=True)
Set ws2 = wb2.Worksheets("Sheet1")
Else
MsgBox "CCCardExpense.xls not found."
Exit Sub
End If

With wb2
.Activate
'Run your code making sure to qualify workbook and ranges of
cccardexpense
ws.Range("A2").Value = ws2.Range("A10").Value ' example
End With

wb2.Close savechanges:=False
Application.ScreenUpdating = True
End Sub