Open Excel file from IDEA script

6 posts / 0 new
Log in or register to post comments
Last post
Thu, 07/25/2019 - 11:10
#1
Robert van den ...
Offline
Joined: 08/08/2018 - 07:37
Open Excel file from IDEA script

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

Top
Fri, 07/26/2019 - 02:53
klmi
Offline
Joined: 02/13/2019 - 08:41

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

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

Top
Wed, 07/31/2019 - 11:20
(Reply to #2)
Robert van den ...
Offline
Joined: 08/08/2018 - 07:37

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?

Files: 
Binary Data Import Excel to array.iss
Top
Thu, 08/01/2019 - 07:23
(Reply to #3)
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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

Top
Thu, 08/01/2019 - 10:42
(Reply to #4)
Robert van den ...
Offline
Joined: 08/08/2018 - 07:37

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. 
 

Files: 
Binary Data Excel files Edit.iss
Top
Thu, 08/01/2019 - 14:46
(Reply to #5)
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

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
Top