Here is the sample code I use for importing a text macro file(.bas file) to a workbook, run specified module at that .bas , and then drop it at run-time dynamically. Hope it helps.

Chris Lee


define class import_excel_macro_and_run as session
procedure go
lparameters po_workbook, pc_macro_file_name, pc_module_name
local lo_project, lo_module, i, t, ll_find_module

with po_workbook.application
po_workbook.save()

ll_find_module = .f.
t = .VBE.VBProjects.count
for i = 1 to t
lo_project = .VBE.VBProjects(i)
if type("lo_project.filename") <> "C"
loop
endif

if lo_project.filename = po_workbook.fullname
lo_project.VBComponents.Import(pc_macro_file_name)
lo_module = lo_project.VBComponents(lo_project.VBComponents.Count)
ll_find_module = .t.
exit
endif
endfor

if ll_find_module
.run(pc_module_name)
lo_project.VBComponents.Remove(lo_module)
endif
endwith
endproc
enddefine


>I create a report by moving data from fox into an excel template via automation. I then invoke macros in the template to copy the data over and do a bunch of formatting. I would like to drop the macro from the template before I save the file for distribution. That way I can avoid the pesky nag screen that appears when you open an excel file with a macro in it ... makes my boss uncomfortable. :)
>
>Problem is I am not sure exactly how to do that. Here is a simplified version of what I am doing:
>
>
>local tcXLSAPP
>
>* Open excel
>tcXLSAPP = createobject("Excel.Application")
>tcXLSAPP.visible = .t.
>tcXLSAPP.workbooks.open("MyTemplate.xls") && Excel template
>
>* Insert data and Format report
>wait window "Moving data to Excel from file " + m.tcFileName nowait
>tcXLSAPP.worksheets.item("Paste Data").select
>tcXLSAPP.run( "InsertWorksheet", "SourceData.xls" ) && Macro:Data to template
>tcXLSAPP.run( "FormatData" ) && Format data macro
>
>* do something here to drop the macros
>
>*Save report
>tcXLSAPP.ActiveWorkbook.saveas("MyReport.xls")
>
>* Close Excel
>tcXLSAPP.quit
>tcXLSAPP = .null.
>release m.tcXLSAPP
>
>Thanks in advance!