Calling a function from another Excel workbook

Question

I've got a pile of data in one worksheet that I am trying to save to individual workbooks based on values in several columns. The approach I am taking (for better or worse!) is to copy the relevant worksheet (and macros) to a new workbook, save it with an appropriate name (let's say temp.xlsx), and then to cleanse the data in that new workbook by deleting irrelevant rows (function called deleteInfo). This all has to be done without altering the original workbook, as per company policy.

I can copy the stuff over no problem, but I'm having serious issues calling macros in the new workbook then.

I have tried:

Application.Run "'temp.xlsx'!deleteInfo"

ActiveWorkbook.Application.Run deleteInfo

Application.Run ("'C:\user\.....\temp.xlsx'!deleteInfo")

But none have worked.

Answer 1

For the task like this you should consider creating an Excel add-in (file extension .xla) containing VBA macros while keeping the regular Workbooks with data macro-free (extension .xls or .xlsx). More details in Microsoft online article: https://support.office.com/en-ca/article/Add-or-remove-add-ins-0af570c4-5cf3-4fa9-9b88-403625a0b460 Hope this may help.

Answer 2

Solved this issue by exporting the module in which the macro was saved, copying the original workbook and importing it into the new workbook. pathName was defined in previous module to this as the path to the original file's folder (pathName = ActiveWorkbook.Path)

Sub exportMacro(ByVal pathName As String)
   'Export the macro to save as .bas file

    On Error Resume Next
    Kill pathName & "\Module6.bas" 'Delete previously exported file
    On Error GoTo 0
    ActiveWorkbook.VBProject.VBComponents("Module6").Export pathName & "\Module6.bas"
End Sub


Sub importMacro(ByVal pathName As String)
    'import the macro to a new workbook

     ActiveWorkbook.VBProject.VBComponents.Import pathName & "\Module6.bas"

End Sub