General information
Category:
COM/DCOM and OLE Automation
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!
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only