Using VB.NET iLogic code to get data from excel documents

[HansV]

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.

[hmk999]

Thank You Hans, that's really helpful I really appreciate your help.



Regards
hmk999

[hmk999]

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

[HansV]

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

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

[hmk999]

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

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()

[HansV]

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.

[hmk999]

Hans, thanks for your explanation throughout all the code, its been a really big help and much appreciated.

Regards
hmk999

[hmk999]

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

Hans, I have gone through the code over and over and I have one more question, I understand your explanation how the code works, but what I don't understand.... is say I select a document with 26 steps, then all the relevant data is created via the loop, then say I select a document with 4 steps, what happens is, all the data for the relevant 4 steps is created via the loop BUT then from steps 5 to step 26, it keeps all the values from the previous 26 step document and all the values/parameters are active, meaning it works every time. I cant see where in the code or how this works in the code? how it keeps the cells with no value, active from the previously used doc?

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

[HansV]

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.

[hmk999]

Hans thanks again for your explanation, that's a big help.

Regards
hmk999