Open Excel file from IDEA script
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
Yes it is but it is not the
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
Hi, I got a script working
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.
I always have to break it
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
Hi Robert,
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