-1

My application exports an .xls with some data.

How can i manage to put some VB script to an exported xls from Delphi.

Explanation:

I'm using CreateOleObject('Excel.Application') for creating it.

That .xls will be changed by the user and imported in application again.

I want that any row that gets changed by the user, be marked as so.

I was able to achieve that in Excel with the script below, but I'm not able to insert that script at the document creation.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row > 1 Then Cells(Target.Row, "I") = Now()
End Sub

Any suggestion to solve that problem in a different way will be welcome.

Ken White
  • 123,280
  • 14
  • 225
  • 444
JGBMattos
  • 1
  • 1
  • What ` script above` are you talking about? Iac, why can't you create some VBA script which does what you want and then translate it into Delphi Ole code? – MartynA Oct 16 '18 at 21:38
  • Start [here](https://learn.microsoft.com/en-us/office/vba/api/excel.application.vbe) which returns [this](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/vbe-object-vba-add-in-object-model). Documentation is kind of odd, [here](https://support.microsoft.com/en-ca/help/219905/how-to-dynamically-add-and-run-a-vba-macro-from-visual-basic) is an example from VB. – Sertac Akyuz Oct 16 '18 at 23:00
  • FWIW this is VBA and not VBscript which are different. What you need to do is to insert that VBA into the document. – David Heffernan Oct 17 '18 at 03:16
  • You're looking for `ExcelWorkSheet.Scripts.Add`. That should give you a start at a search, either for Delphi code or via the Excel Object Model documentation at MSDN. – Ken White Oct 17 '18 at 12:34
  • @KenWhite. I spent quite a while trying to retrieve the contents of the `Scripts` property of a WorkSheet that I knew to contain VBA routives (`sub`s) and never managed to get anything. Eventually, I stumbled upon this: https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.core.script?view=office-pia, which seems to suggest that `Scripts` are about HTML rather than VBA. I think the "way in" to the answer to the OP's q might be through the objects in the VBIDE_Tlb unit but haven't got my head around that yet. – MartynA Oct 18 '18 at 21:50
  • @MartynA: Scripts was taken from the Excel declarations for Office 2000, and is available in the early bindings (dropping a `TExcelWorksheet` component on the form). It seems to be documented in the Excel Object model at MSDN, and it can be read (and written to) by XLSReadWriteII v6 from Axolot, although I haven't used it for that purpose. (I also haven't looked into Scripts except by perusing the source and MSDN docs), so I can't provide an example either. I personally would do this in my code rather than via VBA (you know what you exported, and can compare to what comes back). – Ken White Oct 18 '18 at 22:01
  • I was able to solve my problem, special thanks to @KenWhite. – JGBMattos Oct 23 '18 at 19:20
  • @MartynA I really don't know if we understood each other. I saw that i can write my on methods (On delphi) for excel events, like the one i put in the description in VBA. But those methods written in delphi are exported to the generated xls?, or they are only used in i dont know, in memory xls manipulation from some delphi's application? – JGBMattos Oct 23 '18 at 19:23

1 Answers1

0

I was able to solve that question with the code bellow:

ExcApp := CreateOleObject('Excel.Application');
ExcApp.Visible := False;
ExcApp.WorkBooks.Add;
xVBComponente := ExcApp.WorkBooks[1].VBProject.VBComponents.Item('Planilha1');
xVBComponente.CodeModule.AddFromString(xMacro);
ExcApp.WorkBooks[1].SaveAs(pFileName, 52);

The 52 in "...SaveAs.." is the constant "xlOpenXMLWorkbookMacroEnabled".

Besides the code i needed it to change some excel configs. link for details: Excel Config

JGBMattos
  • 1
  • 1