IDEAScript for exporting a database to an Excel file and insert a VBA macro

amunoz

Forums

Hi friends.
I need a sample IDEAScript that export a database to an Excel file and insert a VBA macro in that file.
Thank you very much in advance.
Regards from Nicaragua.
 
 

Brian Element Fri, 12/22/2017 - 13:27

Hi Amunoz,

I am not sure if this is possible using IDEAScript.  I know I can create Excel spreadsheets using IDEAScript but I don't know how to also insert a VBA macro into the spreadsheet.

Hopefully someone else might have some experience doing that.

Brian

DanHoep Mon, 09/17/2018 - 05:46

Hi there,
it might be bit of a late answer, but just in case anybody else looks this up here is my answer to it:
Although you are generally able to manipulate code by running code in the VBA integrated development environment, access to the VB-Project (the macro-part of an Excel-File) is protected to be fiddled with from the outside. To be able to manipulate code, you'll have to set the "Trust access to the VBProject"-Setting inside the VBE (and you should be certain that you really want to trust codemanipulation). I know of no way to use latebinding to access the IDE from the outside. And there is a good reason for this, as this would open up gates to hell, if you could manipulate any Excel-File to contain malicious code.
Anyhow, you could use a xltm-template-file, which already contains the needed VBA-Code and export the IDEA-DB to a new instance of that file:
Option Explicit

'Export the Current Database into a macro-enabled Excel-Template

Sub Main
Dim DB As DataBase
Dim tmpFileName As String
Dim xlApp As Object
Dim wbSource As Object
Dim wbTarget As Object

Set DB = Client.CurrentDatabase
tmpFileName = "C:\Daten\tmpxlsx.XLSx"
If ExportMyDB (DB, tmpFileName) Then

Set xlApp = CreateObject("Excel.Application")
Set wbSource = xlapp.Workbooks.Open(tmpFileName)
Set wbTarget = xlapp.Workbooks.Add(Template:="C:\Data\VBA\TemplateFileForIDEA.xltm")

wbSource.Worksheets("Database").Copy Before:= wbTarget.Worksheets(1)
wbTarget.SaveAs "C:\Data\TheExported.xlsm", 52 'XlFileFormat.xlOpenXMLWorkbookMacroEnabled
wbTarget.Close False
wbSource.Close False
xlApp.Quit
Kill tmpFileName
Else
MsgBox "Oh no, the export did not work out"
end if
End Sub

Function ExportMyDB(DB As Database, tmpFileName As String) As Boolean
Dim task As Object
Set Task = DB.ExportDatabase
Task.IncludeAllFields
Task.PerformTask tmpFileName, "Database","XLSX", 1, db.Count,""
ExportMyDB = Dir(tmpFileName) <>""
End Function


HTH and good luck
Daniel