Free Support Forum - aspose.com

Run excel macro in a run time

Hi,

Please let me know if I can run excel macro in run time using Aspose.Cells.

Thanks.

No, this feature is not supported.

Do you have plans to include it in the future release?

No. We won’t make this feature because we don’t have any detail information about VBA/macros.

In some cases I have to run macro from template file in a run time.

So, I am trying to do following:

Dim oWB As Aspose.Cells.Workbook

oWB = New Workbook()

oWB.Open(strReportNameAndPath, FileFormatType.Excel2000)

… Formating …

oWB.CalculateFormula()

oWB.Save(strReportNameAndPath, FileFormatType.Excel2000)

oWB = Nothing

Run macro using Excel.Application object:

Dim oXL As Excel.Application

Dim oExcelWB As Excel.Workbook

Dim oWBTemplate As Excel.Workbook

Dim oExcelSheet As Excel.Worksheet

Dim oTemplateSheet As Excel.Worksheet

oXL = CType(CreateObject("Excel.Application"), Excel.Application)

oExcelWB = oXL.Workbooks.Open(strReportNameAndPath)

oWBTemplate = oXL.Workbooks.Open(strExcelTemplateLocal)

oExcelWB.Sheets.Add()

oXL.Workbooks(1).Activate()

oXL.Run(strExcelTemplate & "!PostQuery")

oExcelWB.Save()

oXL.Workbooks.Close()

oExcelSheet = Nothing

oExcelWB = Nothing

oXL.Quit()

oXL = Nothing

After I opened final excel file, it does not have macro changes.

If I use SaveAS:

oExcelWB.SaveAs("c:\test.xls")

I can see all macro changes in "c:\test.xls".

Looks like Aspose.Cells not releasing the file after:

oWB.Save(strReportNameAndPath, FileFormatType.Excel2000)

oWB = Nothing

Please help.

Thanks.

I don’t know why Excel automation has this difference with Save() and SaveAs() method. Sure Aspose.Cells releases the file after calling Save method. You can use MS Excel to edit the file and overwrite it.

Actually, it looks like Aspose.Cells is not releasing the file.

When I am in the debug mode, go to the next line after

oWB.Save(strReportNameAndPath, FileFormatType.Excel2000)

oWB = Nothing

and after that try to open the file, it is not opening until I press F5 to finish run the app.

So it is definitely looks like Aspose.Cells is not releasing the file and MS Excel does not have full access to the file.

Please help.

Thanks.

Which version of Aspose.Cells are you using? And is you application a web app?

I use the following code to open and save a file. And it works fine. I can open the output file after calling Workbook.Save method. I think maybe your Excel automation code locks the file.

Dim wb as Workbook = new Workbook()

wb.Open("d:\test\book1.xls")

wb.Save("d:\test\abc.xls")

wb = Nothing

I am using version 4.2.0.1 (5/3/07 12:02 PM) of Aspose.Cells.

My app is vb.net application.

I am trying to open file after Workbook.Save method and before I run Excel automation code.

I cannot open the file right after Workbook.Save method.

That is why I think Aspose.Cells is not releasing the file and MS Excel does not have full access to the file.

Is your application a web application, winform application or a console application? And please try to remove all Excel automation code from you program.

You can also try to create a console application with my sample code to see if this problem still occurs in your machine.

Sounds like you’re using a stream and not closing it properly. Put a using statement around the stream. If you’re not using streams, then I haven’t got a clue!