Open Excel file from IDEA script

Robert van den…

Forums

I can't get the IDEA script to open a Excel file, i also can't find the answer on Google. Is there anyone with some experience with this?
 
Sub Main
Dim Workbooks As Object
Dim ActiveWorkbook As Object
Dim File As String
File = "H:\!OHW\IDEA\Test Project 01 (9999990)\Import Definitions.ILB\Import definitions.xlsx"
Set Workbooks = CreateObject("Excel.Sheet")
Workbooks.Application.Visible = True
Workbooks.Open (File)
 
'Workbooks.Application.Quit
'Set Workbooks = Nothing
MsgBox "Gereed"
End Sub

klmi Fri, 07/26/2019 - 02:53

Hi Robert,
maybe the following post will help you: http://ideascripting.com/wiki/how-read-excel-worksheets-through-ideascr…

Otherwise I can confirm that code works:


Sub Main
Dim oApp As Object
Dim oBook As Object
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
Set oBook = oApp.Workbooks.Open("H:\!OHW\IDEA\Test Project 01 (9999990)\Import Definitions.ILB\Import definitions.xlsx")
Set oApp = Nothing
Set oBook = Nothing
End Sub

Regards klmi

Robert van den… Wed, 07/31/2019 - 11:20

thanks for your reaction, i created a script to read the content of a Excel file into an array (see the attached file). Is it possible to make a database file (IMD file) from that array?

Brian Element Thu, 08/01/2019 - 07:23

Yes it is but it is not the easiest thing to do.  In the language browser check out Client.NewDatabase for the code.  I usually do this in two parts, I create the database with the fields in it and then I add the information in the fields, usually through two functions.  Also after you have added the rows to your database you need to close and open your database for the information to appear, if you don't do this you will think nothing has happened, took me awhile to figure that one out.

Brian

Robert van den… Thu, 08/01/2019 - 10:42

Hi, I got a script working where the data from the Excel is put into an array and then into a new database (took me this entire day to get it right, see the attached file for the script). The only problem I have now is that the new database is editable where I can edit and delete the values even when the NewTable.Protect is set to True. The AddedField.Type is set to WI_CHAR_FIELD, I tried VIRT_CHAR but then IDEA crashes directly. Any suggestions? I don't really like the idea that someone can change the values in the database/IMD file. 
 

Brian Element Thu, 08/01/2019 - 14:46

I always have to break it down into two functions to get it to work properly.  I have attached an example using your scenario.

What happens is it turns the field into an edit field and if you check in the history the changes are noted.


Dim Array1(10) As String

Sub Main
	Array1(0) = "0"
	Array1(1) = "1"
	Array1(2) = "2"
	Array1(3) = "3"
	Array1(4) = "4"
	Array1(5) = "5"
	Array1(6) = "6"
	Array1(7) = "7"
	Array1(8) = "8"
	Array1(9) = "9"
	Array1(10) = "10"
	
	Call CreateDatabase("Array.IMD")
	Call AddInfo("Array.IMD")
End Sub

Function AddInfo(DatabaseName As String)
	Dim db As database
	Dim table As tableDef
	Dim rec As record
	Dim rs As recordSet
	Dim Q As Integer
	Dim i As Integer
	
	Set db = client.OpenDatabase(DatabaseName)
		Set table = db.tableDef
			table.protect = False
			Set rs = db.RecordSet		
				Q = UBound(Array1)
				For i = 0 To Q
					Set rec = rs.NewRecord
					rec.SetCharValue "Instantie", Array1(i)
					rs.AppendRecord rec
				Next i

			Set rs = Nothing
			table.protect = True
		Set table = Nothing
		db.close
	Set db = Nothing
End Function

Function CreateDatabase(DatabaseName As String)
	Dim NewTable As Table
	Set NewTable = Client.NewTableDef
	Dim AddedField As Field
	
	Set AddedField = NewTable.NewField
	AddedField.Name = "Instantie"
	AddedField.Type = WI_CHAR_FIELD
	AddedField.Length = 100
	NewTable.AppendField AddedField
	
	NewTable.Protect = False
	Dim db As Database
	Set db = Client.NewDatabase(DatabaseName, "", NewTable)
		db.CommitDatabase
		db.close
	Set db = Nothing
	Set AddedField = Nothing
	Set NewTable = Nothing
End Function