The line
excelApp = GoExcel.Application
starts a hidden instance of Excel from iLogic; the variable excelApp represents the Excel Application. We can use this variable to control Excel from iLogic.
The following lines of code are taken straight from the original code for Excel, but instead of plain Workbooks.Open, we use excelApp.Workbooks.Open, because we need to work with the Excel Application that we started.
Just like in the original code, we loop through the rows of the worksheet to find how many rows, starting at Table_Start, have been used, to determine the number of steps.
The main difference is in the part below the comment
' Copy cell values
In the version for Excel, we could calculate the cell address we wanted to copy information to. In iLogic, we want to assign the information from the worksheet to variables of your 3D model: Plate_Width_Leg_1, Plate_Width_Leg_2, Plate_Width_Leg_3, etc.
As far as I know, we cannot refer to those variables with _1, _2, ..., _26 in one step, so the Select Case statement laboriously performs the assignments separately for _1, _2, etc.: when Counter = 1, the value of Plate_Width_Leg_1 is set, when Counter = 2, the value of Plate_Width_Leg_2 is set, etc. etc.
At the end of the code, the workbook is closed without saving it.
Using VB.NET iLogic code to get data from excel documents
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Using VB.NET iLogic code to get data from excel document
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Re: Using VB.NET iLogic code to get data from excel document
Thank You Hans, that's really helpful I really appreciate your help.
Regards
hmk999
Regards
hmk999
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Re: Using VB.NET iLogic code to get data from excel document
Code: Select all
'set excel name string
oExcelName = "W:\Eng\Transformers\7_Engineering_Library\1_Electrical\2_Electrical_Core_Schedules\613xxxx\Development_hmk\" & Core_Schedule & ".xls"
'Write to Core_Circle parameter from the cell called Leg_Ctrs in the excel
Core_Circle = GoExcel.CellValue(oExcelName, "Drawing", "Core_Circle")
Leg_Centres = GoExcel.CellValue(oExcelName, "Drawing", "Leg_Ctrs")
HOW = GoExcel.CellValue(oExcelName, "Drawing", "HOW")
Number_of_Holes = GoExcel.CellValue(oExcelName, "Drawing", "No_of_Holes")
Hole_Diameter = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Diameter")
Hole_Position_A = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_A")
'If no value in cell for Hole_Position_B then use Hole_Position_A value
If (GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")) = Nothing Then 'Or possibility NULL or EMPTY
Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_A")
Else
Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")
End If
For RowNumber = 28 To 53
If Val(GoExcel.CellValue(oExcelName, "Drawing", "K" & RowNumber)) <> 0 Then
NoRow = NoRow + 1
Parameter ("Plate_Width_Leg_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "K" & RowNumber))
Parameter ("Plate_Width_Yoke_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "L" & RowNumber))
Parameter ("HV_Step_Size_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "P" & RowNumber))
Parameter ("LV_Step_Size_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "R" & RowNumber))
Else
Exit For
End If
Next
Number_of_Steps = NoRow
RuleParametersOutput()
InventorVb.DocumentUpdate()
Hi Hans,
I have been playing around with iLogic and I have managed to create some code that's does more or less the same thing as the previous code you help me with last week, the only difference being, that if the cells are blank between steps 4 and 26, it keeps the previous value active. Plus the parameters e.g. LV_Step_Size are parameters created in the 3d model.
The reason I have managed to create the code is because the inventor software gives you options for some snippets of code.
The problem I have is I don't fully understand the part of code below, I understand it searchers between cell 28 to 53, but which part of the code says if theirs no value keep the previous value? at the start of the if statement I'm not sure what <> 0 then means? plus at the very end I'm not sure whats happening, Else Exit For End If Next Number_of_Steps = NoRow
For RowNumber = 28 To 53
If Val(GoExcel.CellValue(oExcelName, "Drawing", "K" & RowNumber)) <> 0 Then
NoRow = NoRow + 1
Parameter ("Plate_Width_Leg_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "K" & RowNumber))
Parameter ("Plate_Width_Yoke_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "L" & RowNumber))
Parameter ("HV_Step_Size_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "P" & RowNumber))
Parameter ("LV_Step_Size_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "R" & RowNumber))
Else
Exit For
End If
Next
Number_of_Steps = NoRow
RuleParametersOutput()
InventorVb.DocumentUpdate()
Would you be able to explain what is happening step by step for me please, the code works every time and gives the same result to my 3d model.
hmk999
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Using VB.NET iLogic code to get data from excel document
Ah! I didn't know that you can specify a parameter as a string using Parameter(stringvalue). That makes it a lot easier!
<> means: "not equal to", so we're checking whether a cell value is different from 0.
I have added comments in the code below
<> means: "not equal to", so we're checking whether a cell value is different from 0.
I have added comments in the code below
Code: Select all
' Loop through rows 28 to 53
For RowNumber = 28 To 53
' Check whether the cell in row RowNumber in column K has a value unequal to 0
If Val(GoExcel.CellValue(oExcelName, "Drawing", "K" & RowNumber)) <> 0 Then
' If so, increase the value of NoRow
NoRow = NoRow + 1
' And set the values of the NoRow'th parameters to the values in column K, L, P and R
Parameter ("Plate_Width_Leg_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "K" & RowNumber))
Parameter ("Plate_Width_Yoke_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "L" & RowNumber))
Parameter ("HV_Step_Size_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "P" & RowNumber))
Parameter ("LV_Step_Size_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "R" & RowNumber))
Else
' Otherwise, stop looping through the rows
Exit For
End If
Next
' After the loop, NoRow indicates the number of rows with a non-zero value in column K, i.e. the number of steps.
Number_of_Steps = NoRow
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Re: Using VB.NET iLogic code to get data from excel document
Thanks Hans, that's really helpful,
I also learnt how to create a text file, the first part of the code is at the very start and the second part at the very end. basically when I run the code it creates or updates the log file, which it logs every time the rule of code the run, just stating the time of day only. Are there other bits of info I can add to the log, such as time & date, or maybe something else. Plus why does the log code have to appear at the beginning and also at the end?
I will be creating many different snippets of code from iLogic and will post the different threads on here for anyone to pick up and use.
Regards
hmk999
I also learnt how to create a text file, the first part of the code is at the very start and the second part at the very end. basically when I run the code it creates or updates the log file, which it logs every time the rule of code the run, just stating the time of day only. Are there other bits of info I can add to the log, such as time & date, or maybe something else. Plus why does the log code have to appear at the beginning and also at the end?
I will be creating many different snippets of code from iLogic and will post the different threads on here for anyone to pick up and use.
Regards
hmk999
Code: Select all
'____Open and append to an existing text file_______
Dim oAppend As System.IO.StreamWriter
oFile = "c:\temp\log.txt"
oAppend = IO.File.AppendText(oFile)
oAppend.WriteLine("core schedule data (start): " & TimeString )
oAppend.Close()
Code: Select all
oAppend = IO.File.AppendText(oFile)
oAppend.WriteLine("core schedule data (end): " & TimeString )
oAppend.Close()
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Using VB.NET iLogic code to get data from excel document
There is no absolute need for the log code to be at the beginning and the end of the code. The example has that because it writes both the time the code starts AND the time the code finishes to the log file.
You do need to execute the lines
Dim oAppend As System.IO.StreamWriter
oFile = "c:\temp\log.txt"
before the first time you write something to the text file. This tells the code what you're going to use.
The line
oAppend = IO.File.AppendText(oFile)
opens the log file. You can then have as many lines with oAppend.WriteLine as you want. Each such line will write one line of text to the log file.
For example:
oAppend.WriteLine("It is currently " & Format(Now, "m/d/yyyy h:mm AM/PM"))
oAppend.WriteLine("We have processed" & Number_of_Steps & " steps")
Finally,
oAppend.Close()
closes the text file.
You do need to execute the lines
Dim oAppend As System.IO.StreamWriter
oFile = "c:\temp\log.txt"
before the first time you write something to the text file. This tells the code what you're going to use.
The line
oAppend = IO.File.AppendText(oFile)
opens the log file. You can then have as many lines with oAppend.WriteLine as you want. Each such line will write one line of text to the log file.
For example:
oAppend.WriteLine("It is currently " & Format(Now, "m/d/yyyy h:mm AM/PM"))
oAppend.WriteLine("We have processed" & Number_of_Steps & " steps")
Finally,
oAppend.Close()
closes the text file.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Re: Using VB.NET iLogic code to get data from excel document
Hans, thanks for your explanation throughout all the code, its been a really big help and much appreciated.
Regards
hmk999
Regards
hmk999
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Re: Using VB.NET iLogic code to get data from excel document
Code: Select all
For RowNumber = 28 To 53
If Val(GoExcel.CellValue(oExcelName, "Drawing", "K" & RowNumber)) <> 0 Then
NoRow = NoRow + 1
Parameter ("Plate_Width_Leg_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "K" & RowNumber))
Parameter ("Plate_Width_Yoke_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "L" & RowNumber))
Parameter ("HV_Step_Size_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "P" & RowNumber))
Parameter ("LV_Step_Size_" & NoRow) = Val(GoExcel.CellValue(oExcelName,"Drawing", "R" & RowNumber))
Else
Exit For
End If
Next
Number_of_Steps = NoRow
I also added the results from the log file, you will see the AM/PM returns A1/P1, not sure why?
Regards
hmk999
Code: Select all
oAppend = IO.File.AppendText(oFile)
oAppend.WriteLine("core schedule data (end) : " & TimeString )
oAppend.WriteLine ("It is currently " & Format(Now, "d/m/yyyy h:mm AM/PM"))
oAppend.WriteLine ("We have processed " & Number_of_Steps & " Steps")
oAppend.Close()
'core schedule data (start): 20:20:10
'core schedule data (End) : 20:20:33
'It Is currently 29/20/2014 8:20 A1/P1
'We have processed 26 Steps
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Using VB.NET iLogic code to get data from excel document
The code simply exits when the cell in column K is empty or zero, so the values of the rest of the parameters remains unchanged. If you would start with a document with only 4 steps, the parameters for steps 5 to 26 would be 0. This is different from the original Excel code, where the parameters for the 'missing' steps would be filled in with the values for the last non-missing step.
In VBA, AM/PM is a valid addition to a time value in the Format function - it will display either "AM" or "PM" dependent on whether the time is before or after 12 noon.
Apparently, this doesn't work in VB.NET - the "M" in AM/PM is interpreted as the month number (currently 1 since it is January).
You can change the line
oAppend.WriteLine ("It is currently " & Format(Now, "d/m/yyyy h:mm AM/PM"))
to
oAppend.WriteLine ("It is currently " & Format(Now, "d/m/yyyy hh:mm"))
to display the time as 10:03 if it is morning, or as 22:03 if it is evening.
In VBA, AM/PM is a valid addition to a time value in the Format function - it will display either "AM" or "PM" dependent on whether the time is before or after 12 noon.
Apparently, this doesn't work in VB.NET - the "M" in AM/PM is interpreted as the month number (currently 1 since it is January).
You can change the line
oAppend.WriteLine ("It is currently " & Format(Now, "d/m/yyyy h:mm AM/PM"))
to
oAppend.WriteLine ("It is currently " & Format(Now, "d/m/yyyy hh:mm"))
to display the time as 10:03 if it is morning, or as 22:03 if it is evening.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Re: Using VB.NET iLogic code to get data from excel document
Hans thanks again for your explanation, that's a big help.
Regards
hmk999
Regards
hmk999