Excel Object Models

Hi Edward,

I am glad you are enjoying using IDEA and continue to find new ways to use it.  If you go to amazon there are several books on using VBA with Excel and Microsoft Office, I learned probably more by playing with it and googling looking for answers for my problems.  I will put together an example script for you to see how you can access the Excel Object and use it in your script and post it, probably sometime this weekend.

Thanks

Brian

Hi Edward, here is an example script. It will take an IDEA file and export the field headings and data to an excel spreadsheet, I figured this would be a good place to start. You can download the code here.

'**************************************************************************************************************
'* Script:		excel_export_demo.iss
'* Author:	Brian Element - brian.element@ideascripting.com
'* Date July 5, 2014
'* Purpose:	Demo script to show how to integrate excel into your scripts.  Will export an  IDEA
'*		file defined in sFilename variable and export the field names and record information
'*		to excel.
'* Warning:	This script is for demo purposes only, use at own risk.
'**************************************************************************************************************

Option Explicit

	Dim sFilename As String

Sub Main
	sFilename = "NigriniCycle&InvoicesData-Data.IMD"
	Call exportToExcel()
End Sub

Function exportToExcel()
	Dim excel As Object
	Dim oBook As Object
	Dim oSheet As Object
	
	Dim db  As database
	Dim rs As recordset
	Dim ThisTable As Object
	Dim field As field
	Dim rec As Object
	Dim i As Long
	Dim j As Integer
	Dim iFieldCount As Integer
	
	Set db = client.OpenDatabase(sFilename)
	Set ThisTable = db.TableDef
	Set rs = db.RecordSet
	
	'create the Excel, workbook and worksheet objects	
	Set excel = CreateObject("Excel.Application")
	Set oBook = excel.Workbooks.Add
	Set oSheet = oBook.Worksheets.Item(1)
	
	'get the field names and put them in the header
	iFieldCount = ThisTable.count
	For i = 1 To ThisTable.count
		Set field = ThisTable.GetFieldAt (i)
		oSheet.Cells(1, i).Value = field.name
		'make the field bold, and larger
		oSheet.Cells(1,i).Font.Bold = True
		oSheet.Cells(1,i).Font.Name = "Calibri"
		oSheet.Cells(1,i).Font.Size = 12
	Next i
	
	'get the record information and insert it into the excel spreadsheet
	For j = 1 To iFieldCount
		Set field = ThisTable.GetFieldAt (j) 
		rs.ToFirst
		For i = 1 To db.count
			Set rec = rs.ActiveRecord
			rs.Next
			
			If field.IsCharacter Then 
				oSheet.Cells(i + 1, j).Value = rec.GetCharValueAt (j) 
			ElseIf field.IsNumeric Then 
				oSheet.Cells(i + 1, j).Value = rec.GetNumValueAt(j) 
			ElseIf field.IsDate Then 
				oSheet.Cells(i + 1, j).Value = rec.GetDateValueAt(j) 
			ElseIf field.IsTime Then 
				oSheet.Cells(i + 1, j).Value = rec.GetTimeValueAt(j) 
			End If
		Next i
	Next j
	
	'autofit the columns
	For i = 1 To  iFieldCount
		oSheet.Columns(i).EntireColumn.AutoFit
	Next i

	excel.Visible = True
	
	Set db = Nothing
	Set rs = Nothing
	Set rec = Nothing
	Set field = Nothing
	Set ThisTable = Nothing
	Set excel = Nothing
	Set oBook = Nothing
	Set oSheet = Nothing
End Function