Using VB.NET iLogic code to get data from excel documents
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Using VB.NET iLogic code to get data from excel documents
Hi, I am a new starter using iLogic VB.NET
I have defined some parameters in a 3d model using inventor software. The parameters are listed as follows:
Core_Schedule
Core_Circle
Leg_Centres
HOW
Number_of_Holes
Hole_Diameter
Hole_Position_A
Hole_Position_B
note the parameter Core_Schedule allows me to select the excel sheet I want and is a multivalue
I want to get some data from various excel files linking cells to above parameters. The cell descriptions in excel are listed as follows:
Core_Circle
Leg_Ctrs
HOW
No_of_Holes
Hole_Diameter
Hole_Position_A
Hole_Position_B
below is my code so far that works fine by getting the value from the excel doc, linking to 3d model parameter.
oExcelName = "C:\My Documents\Core & Clamps Development\Development_hmk\" & Core_Schedule & ".xls"
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")
The problem I have is the next line of code should be: Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")
However, some excel docs have a value in cell name Hole_position_B, but some docs don't have any value and just blank cell, what I want to do is an if statement to say If there is no value in cell Hole_position_B, it will use the value from Hole_position_A. but still link this to model parameter Hole_Position_B
Can anyone show me how to do this
Thanks
hmk999
I have defined some parameters in a 3d model using inventor software. The parameters are listed as follows:
Core_Schedule
Core_Circle
Leg_Centres
HOW
Number_of_Holes
Hole_Diameter
Hole_Position_A
Hole_Position_B
note the parameter Core_Schedule allows me to select the excel sheet I want and is a multivalue
I want to get some data from various excel files linking cells to above parameters. The cell descriptions in excel are listed as follows:
Core_Circle
Leg_Ctrs
HOW
No_of_Holes
Hole_Diameter
Hole_Position_A
Hole_Position_B
below is my code so far that works fine by getting the value from the excel doc, linking to 3d model parameter.
oExcelName = "C:\My Documents\Core & Clamps Development\Development_hmk\" & Core_Schedule & ".xls"
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")
The problem I have is the next line of code should be: Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")
However, some excel docs have a value in cell name Hole_position_B, but some docs don't have any value and just blank cell, what I want to do is an if statement to say If there is no value in cell Hole_position_B, it will use the value from Hole_position_A. but still link this to model parameter Hole_Position_B
Can anyone show me how to do this
Thanks
hmk999
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Using VB.NET iLogic code to get data from excel document
Hi hmk999,hmk999 wrote:Hole_Position_A = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_A")
The problem I have is the next line of code should be: Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")
However, some excel docs have a value in cell name Hole_position_B, but some docs don't have any value and just blank cell, what I want to do is an if statement to say If there is no value in cell Hole_position_B, it will use the value from Hole_position_A. but still link this to model parameter Hole_Position_B
hmk999
Welcome to Eileen's Lounge.
I cannot say that this in my field of expertise so anything I write is a complete guess (based of typical program flow from VB)
Have you tried any of the following statements:
Code: Select all
IF (GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")) = "" Then
Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_A")
ELSE
Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")
END IF
Code: Select all
IF (GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")) = EMPTY Then 'Or possibility NULL
Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_A")
ELSE
Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")
END IF
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Re: Using VB.NET iLogic code to get data from excel document
Hi Rudi, thanks very much for your help, the second option of your code works every time. I have one other task I’m trying to achieve which is an addition to the code you have already helped me with.
I have another 104 parameters in my 3d model that I wish to link to data in excel file.
The names of the parameters are as follows:
Plate_Width_Leg_1 Plate_Width_Yoke_1 HV_Step_Size_1 LV_Step_Size_1
Plate_Width_Leg_2 Plate_Width_Yoke_2 HV_Step_Size_2 LV_Step_Size_2
Plate_Width_Leg_3 Plate_Width_Yoke_3 HV_Step_Size_3 LV_Step_Size_3
Continue parameter until up to 26 for each = 104 parameters
In all of the excel docs, across rows K,L,PQ,RS, note PQ and RS have merged cells, there is always between 4 and 26 rows of data, I want to link this data to my 3D model parameters.
e.g. Column K28 = Plate_Width_Leg_1 Column L28 = Plate_Width_Yoke_1
Column P&Q28 = HV_Step_Size_1 Column R&S28 = LV_Step_Size_1
Example 1 showing 4 rows of data
Cell K28 L28 P&Q28 R&S28
Value 400 400 25 25
Cell K29 L29 P&Q 29 R&S 29
Value 300 300 20 20
Cell K30 L30 P&Q 30 R&S 30
Value 200 200 15 15
Cell K31 L31 P&Q 31 R&S 31
Value 150 150 10 10
Example 2 showing 26 rows of data (just typed the four out indicating the last cell being row 53).
Cell K28 L28 P&Q 28 R&S 28
Value 400 400 25 25
Cell K29 L29 P&Q 29 R&S 29
Value 300 300 20 20
Cell K30 L30 P&Q 30 R&S 30
Value 200 200 15 15
Cell K53 L53 P&Q 53 R&S 53
Value 150 150 10 10
Notes:
1.Not sure if they all start at K28 every time but I have named the cell Table_Start in all my excel doc’s which will be position K28 in examples 1 & 2 above
2.If there are less than 26 rows of data in any excel doc, can the last value fill in the remaining parameters so there is always a value? e.g. in example 1, the last K value is 150 at K31, so I want all the model parameters between Plate_Width_Leg_1 and Plate_Width_Leg_26 to show 150 as the value. and have the same rule for columns L,P&Q and R&S
3.In my 3d model I have created one more parameter called Number_of_Steps, I want to be told how many steps there are in that parameter, e.g. 1 step = 1 row, so in example 1 we have 4 steps, in example 2 we have 26 steps.
4.The data columns will all start and end on the same row. However there is hidden data below the last step.
5.In some not all of my excel docs, there are two Asterisk as well as a value at the location of the last two steps, down K column. e.g. 70 * or 190 * Can you modify my code so it just collects the number and not the Asterisk? More info, the Asterisk will only ever appear on the last two steps of the K column but
there can be between 4 and 26 steps. It can be a two or three digit number e.g. 70 * or 190 *. Also there will always be a space between the last number and the Asterisk.
Hope all this makes sense, let me know if you can help.
Additional information, here is my latest code in VB.NET that works every time
oExcelName = "C:\My Documents\Core & Clamps Development\Development_hmk\" & Core_Schedule & ".xls"
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 (GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")) = Nothing Then
Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_A")
Else
Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")
End If
I also have the code I’m trying to create in VBA which was getting the data from the excel docs, but putting it in a master excel doc which I no longer require. I have shown the code below for reference because I’m trying to achieve the same thing in my 3d model, if you can read the code it may help as I want exactly the same thing linked to my model parameters.
Thanks
Hmk999
Previous code I created in VBA
I have another 104 parameters in my 3d model that I wish to link to data in excel file.
The names of the parameters are as follows:
Plate_Width_Leg_1 Plate_Width_Yoke_1 HV_Step_Size_1 LV_Step_Size_1
Plate_Width_Leg_2 Plate_Width_Yoke_2 HV_Step_Size_2 LV_Step_Size_2
Plate_Width_Leg_3 Plate_Width_Yoke_3 HV_Step_Size_3 LV_Step_Size_3
Continue parameter until up to 26 for each = 104 parameters
In all of the excel docs, across rows K,L,PQ,RS, note PQ and RS have merged cells, there is always between 4 and 26 rows of data, I want to link this data to my 3D model parameters.
e.g. Column K28 = Plate_Width_Leg_1 Column L28 = Plate_Width_Yoke_1
Column P&Q28 = HV_Step_Size_1 Column R&S28 = LV_Step_Size_1
Example 1 showing 4 rows of data
Cell K28 L28 P&Q28 R&S28
Value 400 400 25 25
Cell K29 L29 P&Q 29 R&S 29
Value 300 300 20 20
Cell K30 L30 P&Q 30 R&S 30
Value 200 200 15 15
Cell K31 L31 P&Q 31 R&S 31
Value 150 150 10 10
Example 2 showing 26 rows of data (just typed the four out indicating the last cell being row 53).
Cell K28 L28 P&Q 28 R&S 28
Value 400 400 25 25
Cell K29 L29 P&Q 29 R&S 29
Value 300 300 20 20
Cell K30 L30 P&Q 30 R&S 30
Value 200 200 15 15
Cell K53 L53 P&Q 53 R&S 53
Value 150 150 10 10
Notes:
1.Not sure if they all start at K28 every time but I have named the cell Table_Start in all my excel doc’s which will be position K28 in examples 1 & 2 above
2.If there are less than 26 rows of data in any excel doc, can the last value fill in the remaining parameters so there is always a value? e.g. in example 1, the last K value is 150 at K31, so I want all the model parameters between Plate_Width_Leg_1 and Plate_Width_Leg_26 to show 150 as the value. and have the same rule for columns L,P&Q and R&S
3.In my 3d model I have created one more parameter called Number_of_Steps, I want to be told how many steps there are in that parameter, e.g. 1 step = 1 row, so in example 1 we have 4 steps, in example 2 we have 26 steps.
4.The data columns will all start and end on the same row. However there is hidden data below the last step.
5.In some not all of my excel docs, there are two Asterisk as well as a value at the location of the last two steps, down K column. e.g. 70 * or 190 * Can you modify my code so it just collects the number and not the Asterisk? More info, the Asterisk will only ever appear on the last two steps of the K column but
there can be between 4 and 26 steps. It can be a two or three digit number e.g. 70 * or 190 *. Also there will always be a space between the last number and the Asterisk.
Hope all this makes sense, let me know if you can help.
Additional information, here is my latest code in VB.NET that works every time
oExcelName = "C:\My Documents\Core & Clamps Development\Development_hmk\" & Core_Schedule & ".xls"
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 (GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")) = Nothing Then
Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_A")
Else
Hole_Position_B = GoExcel.CellValue(oExcelName, "Drawing", "Hole_Position_B")
End If
I also have the code I’m trying to create in VBA which was getting the data from the excel docs, but putting it in a master excel doc which I no longer require. I have shown the code below for reference because I’m trying to achieve the same thing in my 3d model, if you can read the code it may help as I want exactly the same thing linked to my model parameters.
Thanks
Hmk999
Previous code I created in VBA
Code: Select all
Sub GetData()
Const TargetRow = 2 ' Paste into row 2
Const TargetCol = 8 ' Start pasting in column H
Const MaxSteps = 26
Dim wbkS As Workbook
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim strFile As String
Dim lngStart As Long
Dim lngEnd As Long
Dim lngSteps As Long
Dim lngStartCol As Long
Dim lngHVCol As Long
Dim lngLVCol As Long
Dim i As Long
Dim r As Long
With Application.FileDialog(1) ' msoFileDialogOpen
.Filters.Clear
.Filters.Add "Excel workbooks", "*.xls;*.xlsx;*.xlsb;*.xlsm"
.InitialFileName = "W:\Eng\Transformers\7_Engineering_Library\1_Electrical\2_Electrical_Core_Schedules\613xxxx\Development_hmk"
If .Show Then
strFile = .SelectedItems(1)
Else
MsgBox "No file selected", vbCritical
Exit Sub
End If
End With
Application.ScreenUpdating = False
Set wshT = ActiveSheet
Set wbkS = Workbooks.Open(Filename:=strFile, ReadOnly:=True)
Set wshS = wbkS.Worksheets(1)
wshT.Cells(TargetRow, 1).Value = wshS.Range("Core_Circle").Value
wshT.Cells(TargetRow, 2).Value = wshS.Range("Leg_Ctrs").Value
wshT.Cells(TargetRow, 3).Value = wshS.Range("HOW").Value
wshT.Cells(TargetRow, 5).Value = wshS.Range("No_of_Holes").Value
wshT.Cells(TargetRow, 6).Value = wshS.Range("Hole_Diameter").Value
wshT.Cells(TargetRow, 7).Value = wshS.Range("Hole_Position_A").Value
If wshS.Range("Hole_Position_B").Value = "" Then
wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_A").Value
Else
wshT.Cells(TargetRow, 8).Value = wshS.Range("Hole_Position_B").Value
End If
' Get first data row
lngStart = wshS.Range("Table_Start").Row
lngStartCol = wshS.Range("Table_Start").Column
lngHVCol = wshS.Range("Progressive_HV").Column
lngLVCol = wshS.Range("Progressive_LV").Column
' Get last data row
For i = 1 To MaxSteps - 1
If wshS.Range("Table_Start").Offset(RowOffset:=i).Value = "" Then
Exit For
End If
Next i
lngEnd = lngStart + i - 1
' Calculate number of steps
lngSteps = i
' Always fill MaxSteps columns
For i = 1 To MaxSteps
' Increase row number up to last data row
If i <= lngSteps Then
r = lngStart + i - 1
End If
' Copy cell values
wshT.Cells(TargetRow, TargetCol + i).Value = Val(wshS.Cells(r, lngStartCol).Value)
wshT.Cells(TargetRow, TargetCol + MaxSteps + i).Value = wshS.Cells(r, lngStartCol + 1).Value
wshT.Cells(TargetRow, TargetCol + 2 * MaxSteps + i).Value = wshS.Cells(r, lngHVCol).Value
wshT.Cells(TargetRow, TargetCol + 3 * MaxSteps + i).Value = wshS.Cells(r, lngLVCol).Value
Next i
' Enter number of steps
wshT.Cells(TargetRow, 4).Value = lngSteps
' Optional: close source workbook
wbkS.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub
Last edited by HansV on 22 Jan 2014, 22:07, edited 1 time in total.
Reason: to add [code] and [/code] tags
Reason: to add [code] and [/code] tags
-
- 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
Converting the VBA loops to iLogic code would be difficult, if it is possible at all.
I think you should use Excel Automation from iLogic. You can define an Excel Application object as follows
excelApp = GoExcel.Application
(See Excel Data Links functions in iLogic reference)
For example to open a workbook:
strFile = "..." ' specify path and filename
wbkS = excelApp.Workbooks.Open(Filename:=strFile, ReadOnly:=True)
wshS = wbkS.Worksheets(1)
Core_Circle = wshS.Range("Core_Circle").Value
etc. This should let you use the VBA code with only minor modifications - make sure that all Excel objects refer either directly or indirectly to excelApp.
I think you should use Excel Automation from iLogic. You can define an Excel Application object as follows
excelApp = GoExcel.Application
(See Excel Data Links functions in iLogic reference)
For example to open a workbook:
strFile = "..." ' specify path and filename
wbkS = excelApp.Workbooks.Open(Filename:=strFile, ReadOnly:=True)
wshS = wbkS.Worksheets(1)
Core_Circle = wshS.Range("Core_Circle").Value
etc. This should let you use the VBA code with only minor modifications - make sure that all Excel objects refer either directly or indirectly to excelApp.
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
Hi Hans,
Using the Excel Automation from iLogic, do you mean still using two excel documents, one being the design spec I select, the other being the master core collecting all the data for the design spec? Or do you mean writing the code in the 3d model inventor iLogic page, which is a VB.NET environment built in to the model.
In the model environment I have created the code below which works every time in the model environment and select the data for which ever design spec I select, still not sure about creating the rest for the loop.
Thanks for your help much appreciated
hmk999
Using the Excel Automation from iLogic, do you mean still using two excel documents, one being the design spec I select, the other being the master core collecting all the data for the design spec? Or do you mean writing the code in the 3d model inventor iLogic page, which is a VB.NET environment built in to the model.
In the model environment I have created the code below which works every time in the model environment and select the data for which ever design spec I select, still not sure about creating the rest for the loop.
Code: Select all
'set excel name string
oExcelName = "C:\My Documents\Core & Clamps Development\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
Thanks for your help much appreciated
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
What I meant is run the code from iLogic. I have no idea whether the following will work, since I don't know either iLogic or VB.NET.
After the code you posted above:
After the code you posted above:
Code: Select all
excelApp = GoExcel.Application
wbkS = excelApp.Workbooks.Open(Filename:=oExcelName, ReadOnly:=True)
wshS = wbkS.Worksheets(1)
TargetRow = 2 ' Paste into row 2
TargetCol = 8 ' Start pasting in column H
MaxSteps = 26
' Get first data row
lngStart = wshS.Range("Table_Start").Row
lngStartCol = wshS.Range("Table_Start").Column
lngHVCol = wshS.Range("Progressive_HV").Column
lngLVCol = wshS.Range("Progressive_LV").Column
' Get last data row
For i = 1 To MaxSteps - 1
If wshS.Range("Table_Start").Offset(i).Value = "" Then
Exit For
End If
Next i
lngEnd = lngStart + i - 1
' Calculate number of steps
Number_of_Steps = i
' Always fill MaxSteps columns
For i = 1 To MaxSteps
' Increase row number up to last data row
If i <= Number_of_Steps Then
r = lngStart + i - 1
End If
' Copy cell values
Select Case i
Case 1
Plate_Width_Leg_1 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_1 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_1 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_1 = wshS.Cells(r, lngLVCol).Value
Case 2
Plate_Width_Leg_2 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_2 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_2 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_2 = wshS.Cells(r, lngLVCol).Value
Case 3
Plate_Width_Leg_3 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_3 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_3 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_3 = wshS.Cells(r, lngLVCol).Value
Case 4
Plate_Width_Leg_4 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_4 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_4 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_4 = wshS.Cells(r, lngLVCol).Value
Case 5
Plate_Width_Leg_5 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_5 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_5 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_5 = wshS.Cells(r, lngLVCol).Value
Case 6
Plate_Width_Leg_6 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_6 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_6 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_6 = wshS.Cells(r, lngLVCol).Value
Case 7
Plate_Width_Leg_7 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_7 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_7 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_7 = wshS.Cells(r, lngLVCol).Value
Case 8
Plate_Width_Leg_8 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_8 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_8 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_8 = wshS.Cells(r, lngLVCol).Value
Case 9
Plate_Width_Leg_9 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_9 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_9 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_9 = wshS.Cells(r, lngLVCol).Value
Case 10
Plate_Width_Leg_10 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_10 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_10 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_10 = wshS.Cells(r, lngLVCol).Value
Case 11
Plate_Width_Leg_11 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_11 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_11 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_11 = wshS.Cells(r, lngLVCol).Value
Case 12
Plate_Width_Leg_12 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_12 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_12 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_12 = wshS.Cells(r, lngLVCol).Value
Case 13
Plate_Width_Leg_13 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_13 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_13 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_13 = wshS.Cells(r, lngLVCol).Value
Case 14
Plate_Width_Leg_14 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_14 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_14 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_14 = wshS.Cells(r, lngLVCol).Value
Case 15
Plate_Width_Leg_15 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_15 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_15 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_15 = wshS.Cells(r, lngLVCol).Value
Case 16
Plate_Width_Leg_16 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_16 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_16 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_16 = wshS.Cells(r, lngLVCol).Value
Case 17
Plate_Width_Leg_17 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_17 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_17 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_17 = wshS.Cells(r, lngLVCol).Value
Case 18
Plate_Width_Leg_18 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_18 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_18 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_18 = wshS.Cells(r, lngLVCol).Value
Case 19
Plate_Width_Leg_19 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_19 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_19 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_19 = wshS.Cells(r, lngLVCol).Value
Case 20
Plate_Width_Leg_20 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_20 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_20 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_20 = wshS.Cells(r, lngLVCol).Value
Case 21
Plate_Width_Leg_21 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_21 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_21 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_21 = wshS.Cells(r, lngLVCol).Value
Case 22
Plate_Width_Leg_22 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_22 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_22 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_22 = wshS.Cells(r, lngLVCol).Value
Case 23
Plate_Width_Leg_23 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_23 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_23 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_23 = wshS.Cells(r, lngLVCol).Value
Case 24
Plate_Width_Leg_24 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_24 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_24 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_24 = wshS.Cells(r, lngLVCol).Value
Case 25
Plate_Width_Leg_25 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_25 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_25 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_25 = wshS.Cells(r, lngLVCol).Value
Case 26
Plate_Width_Leg_26 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_26 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_26 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_26 = wshS.Cells(r, lngLVCol).Value
End Select
Next i
' Optional: close source workbook
wbkS.Close(False)
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, I have run the code you did for me and I get the error message below, any ideas, its obviously excepted the first few lines of code so just need to keep trying. Thanks for your help
Error on Line 33 : Variable 'i' hides a variable in an enclosing block.
For i = 1 To MaxSteps - 1
Error on Line 33 : Variable 'i' hides a variable in an enclosing block.
For i = 1 To MaxSteps - 1
Code: Select all
'set excel name string
oExcelName = "C:\My Documents\Core & Clamps Development\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
excelApp = GoExcel.Application
wbkS = excelApp.Workbooks.Open(Filename:=oExcelName, ReadOnly:=True)
wshS = wbkS.Worksheets(1)
TargetRow = 2 ' Paste into row 2
TargetCol = 8 ' Start pasting in column H
MaxSteps = 26
' Get first data row
lngStart = wshS.Range("Table_Start").Row
lngStartCol = wshS.Range("Table_Start").Column
lngHVCol = wshS.Range("Progressive_HV").Column
lngLVCol = wshS.Range("Progressive_LV").Column
' Get last data row
For i = 1 To MaxSteps - 1
If wshS.Range("Table_Start").Offset(i).Value = "" Then
Exit For
End If
Next i
lngEnd = lngStart + i - 1
' Calculate number of steps
Number_of_Steps = i
' Always fill MaxSteps columns
For i = 1 To MaxSteps
' Increase row number up to last data row
If i <= Number_of_Steps Then
r = lngStart + i - 1
End If
' Copy cell values
Select Case i
Case 1
Plate_Width_Leg_1 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_1 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_1 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_1 = wshS.Cells(r, lngLVCol).Value
Case 2
Plate_Width_Leg_2 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_2 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_2 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_2 = wshS.Cells(r, lngLVCol).Value
Case 3
Plate_Width_Leg_3 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_3 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_3 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_3 = wshS.Cells(r, lngLVCol).Value
Case 4
Plate_Width_Leg_4 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_4 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_4 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_4 = wshS.Cells(r, lngLVCol).Value
Case 5
Plate_Width_Leg_5 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_5 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_5 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_5 = wshS.Cells(r, lngLVCol).Value
Case 6
Plate_Width_Leg_6 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_6 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_6 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_6 = wshS.Cells(r, lngLVCol).Value
Case 7
Plate_Width_Leg_7 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_7 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_7 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_7 = wshS.Cells(r, lngLVCol).Value
Case 8
Plate_Width_Leg_8 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_8 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_8 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_8 = wshS.Cells(r, lngLVCol).Value
Case 9
Plate_Width_Leg_9 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_9 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_9 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_9 = wshS.Cells(r, lngLVCol).Value
Case 10
Plate_Width_Leg_10 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_10 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_10 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_10 = wshS.Cells(r, lngLVCol).Value
Case 11
Plate_Width_Leg_11 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_11 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_11 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_11 = wshS.Cells(r, lngLVCol).Value
Case 12
Plate_Width_Leg_12 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_12 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_12 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_12 = wshS.Cells(r, lngLVCol).Value
Case 13
Plate_Width_Leg_13 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_13 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_13 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_13 = wshS.Cells(r, lngLVCol).Value
Case 14
Plate_Width_Leg_14 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_14 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_14 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_14 = wshS.Cells(r, lngLVCol).Value
Case 15
Plate_Width_Leg_15 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_15 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_15 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_15 = wshS.Cells(r, lngLVCol).Value
Case 16
Plate_Width_Leg_16 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_16 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_16 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_16 = wshS.Cells(r, lngLVCol).Value
Case 17
Plate_Width_Leg_17 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_17 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_17 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_17 = wshS.Cells(r, lngLVCol).Value
Case 18
Plate_Width_Leg_18 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_18 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_18 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_18 = wshS.Cells(r, lngLVCol).Value
Case 19
Plate_Width_Leg_19 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_19 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_19 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_19 = wshS.Cells(r, lngLVCol).Value
Case 20
Plate_Width_Leg_20 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_20 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_20 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_20 = wshS.Cells(r, lngLVCol).Value
Case 21
Plate_Width_Leg_21 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_21 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_21 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_21 = wshS.Cells(r, lngLVCol).Value
Case 22
Plate_Width_Leg_22 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_22 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_22 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_22 = wshS.Cells(r, lngLVCol).Value
Case 23
Plate_Width_Leg_23 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_23 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_23 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_23 = wshS.Cells(r, lngLVCol).Value
Case 24
Plate_Width_Leg_24 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_24 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_24 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_24 = wshS.Cells(r, lngLVCol).Value
Case 25
Plate_Width_Leg_25 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_25 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_25 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_25 = wshS.Cells(r, lngLVCol).Value
Case 26
Plate_Width_Leg_26 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_26 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_26 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_26 = wshS.Cells(r, lngLVCol).Value
End Select
Next i
' Optional: close source workbook
wbkS.Close(False)
-
- 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
What happens if you change all occurrences of i (as a whole word) to something else, e.g. to Counter ?
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
Replace them all but states the same message,
Error on Line 33 : Variable 'Counter' hides a variable in an enclosing block.
Error on Line 33 : Variable 'Counter' hides a variable in an enclosing block.
-
- 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
I'm afraid my lack of knowledge of iLogic and VB.NET shows. I am very sorry, but I can't solve your problem.
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
I will look into this with iLogic rules and let you know how I get on.
Thanks for your help
Regards
hmk999
Thanks for your help
Regards
hmk999
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Re: Using VB.NET iLogic code to get data from excel document
Hans just one more thing that's confused me, in the code I don't understand what the following two lines are doing? as all I am doing is getting the data from a design excel file (1 of 150 ish documents) and linking the values to the parameters in my 3d model, the two lines indicate they are putting information collected from the designs to a separate excel file, Am I misunderstanding something?
TargetRow = 2 ' Paste into row 2
TargetCol = 8 ' Start pasting in column H
Regards
hmk999
TargetRow = 2 ' Paste into row 2
TargetCol = 8 ' Start pasting in column H
Regards
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
Those two lines are left-overs from the original code. They aren't used in the recent version, but I forgot to remove them. You can simply delete those two lines.
Sorry for the confusion!
Sorry for the confusion!
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
OK thanks Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Using VB.NET iLogic code to get data from excel document
Just a suggestion....hmk999 wrote:Hans, I have run the code you did for me and I get the error message below
Error on Line 33 : Variable 'i' hides a variable in an enclosing block.
Is "i" (or "Counter") DIM'ed twice?
IOW : is there two completely different variables in your code that are being run as "i" (or "Counter") during your macro run?
When you replaced "i", make sure that it is only replacing one DIM'ed instance of "i". Don't Find/Replace through the entire module/project.
Also see: (maybe it could help)...
- http://p2p.wrox.com/vb-net/88029-error- ... block.html
- http://msdn.microsoft.com/en-us/library/wtk40des.aspx
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Re: Using VB.NET iLogic code to get data from excel document
Hi Rudi
Can you show me where in my code you would suggest replacing "I" with "Counter" as I'm a little unsure what you mean.
Thanks
hmk999
Can you show me where in my code you would suggest replacing "I" with "Counter" as I'm a little unsure what you mean.
Thanks
hmk999
Code: Select all
'set excel name string
oExcelName = "C:\My Documents\Core & Clamps Development\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
excelApp = GoExcel.Application
wbkS = excelApp.Workbooks.Open(Filename:=oExcelName, ReadOnly:=True)
wshS = wbkS.Worksheets(1)
MaxSteps = 26
' Get first data row
lngStart = wshS.Range("Table_Start").Row
lngStartCol = wshS.Range("Table_Start").Column
lngHVCol = wshS.Range("Progressive_HV").Column
lngLVCol = wshS.Range("Progressive_LV").Column
' Get last data row
For i = 1 To MaxSteps - 1
If wshS.Range("Table_Start").Offset(i).Value = Nothing Then
Exit For
End If
Next i
lngEnd = lngStart + i - 1
' Calculate number of steps
Number_of_Steps = i
' Always fill MaxSteps columns
For i = 1 To MaxSteps
' Increase row number up to last data row
If i <= Number_of_Steps Then
r = lngStart + i - 1
End If
' Copy cell values
Select Case i
Case 1
Plate_Width_Leg_1 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_1 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_1 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_1 = wshS.Cells(r, lngLVCol).Value
Case 2
Plate_Width_Leg_2 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_2 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_2 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_2 = wshS.Cells(r, lngLVCol).Value
Case 3
Plate_Width_Leg_3 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_3 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_3 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_3 = wshS.Cells(r, lngLVCol).Value
Case 4
Plate_Width_Leg_4 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_4 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_4 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_4 = wshS.Cells(r, lngLVCol).Value
Case 5
Plate_Width_Leg_5 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_5 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_5 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_5 = wshS.Cells(r, lngLVCol).Value
Case 6
Plate_Width_Leg_6 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_6 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_6 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_6 = wshS.Cells(r, lngLVCol).Value
Case 7
Plate_Width_Leg_7 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_7 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_7 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_7 = wshS.Cells(r, lngLVCol).Value
Case 8
Plate_Width_Leg_8 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_8 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_8 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_8 = wshS.Cells(r, lngLVCol).Value
Case 9
Plate_Width_Leg_9 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_9 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_9 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_9 = wshS.Cells(r, lngLVCol).Value
Case 10
Plate_Width_Leg_10 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_10 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_10 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_10 = wshS.Cells(r, lngLVCol).Value
Case 11
Plate_Width_Leg_11 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_11 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_11 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_11 = wshS.Cells(r, lngLVCol).Value
Case 12
Plate_Width_Leg_12 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_12 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_12 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_12 = wshS.Cells(r, lngLVCol).Value
Case 13
Plate_Width_Leg_13 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_13 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_13 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_13 = wshS.Cells(r, lngLVCol).Value
Case 14
Plate_Width_Leg_14 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_14 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_14 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_14 = wshS.Cells(r, lngLVCol).Value
Case 15
Plate_Width_Leg_15 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_15 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_15 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_15 = wshS.Cells(r, lngLVCol).Value
Case 16
Plate_Width_Leg_16 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_16 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_16 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_16 = wshS.Cells(r, lngLVCol).Value
Case 17
Plate_Width_Leg_17 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_17 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_17 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_17 = wshS.Cells(r, lngLVCol).Value
Case 18
Plate_Width_Leg_18 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_18 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_18 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_18 = wshS.Cells(r, lngLVCol).Value
Case 19
Plate_Width_Leg_19 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_19 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_19 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_19 = wshS.Cells(r, lngLVCol).Value
Case 20
Plate_Width_Leg_20 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_20 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_20 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_20 = wshS.Cells(r, lngLVCol).Value
Case 21
Plate_Width_Leg_21 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_21 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_21 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_21 = wshS.Cells(r, lngLVCol).Value
Case 22
Plate_Width_Leg_22 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_22 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_22 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_22 = wshS.Cells(r, lngLVCol).Value
Case 23
Plate_Width_Leg_23 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_23 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_23 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_23 = wshS.Cells(r, lngLVCol).Value
Case 24
Plate_Width_Leg_24 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_24 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_24 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_24 = wshS.Cells(r, lngLVCol).Value
Case 25
Plate_Width_Leg_25 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_25 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_25 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_25 = wshS.Cells(r, lngLVCol).Value
Case 26
Plate_Width_Leg_26 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_26 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_26 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_26 = wshS.Cells(r, lngLVCol).Value
End Select
Next i
' Optional: close source workbook
wbkS.Close(False)
-
- 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
Code would look like this:
Code: Select all
'set excel name string
oExcelName = "C:\My Documents\Core & Clamps Development\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
excelApp = GoExcel.Application
wbkS = excelApp.Workbooks.Open(Filename:=oExcelName, ReadOnly:=True)
wshS = wbkS.Worksheets(1)
MaxSteps = 26
' Get first data row
lngStart = wshS.Range("Table_Start").Row
lngStartCol = wshS.Range("Table_Start").Column
lngHVCol = wshS.Range("Progressive_HV").Column
lngLVCol = wshS.Range("Progressive_LV").Column
' Get last data row
Dim Counter
For Counter = 1 To MaxSteps - 1
If wshS.Range("Table_Start").Offset(Counter).Value = Nothing Then
Exit For
End If
Next Counter
lngEnd = lngStart + Counter - 1
' Calculate number of steps
Number_of_Steps = Counter
' Always fill MaxSteps columns
For Counter = 1 To MaxSteps
' Increase row number up to last data row
If Counter <= Number_of_Steps Then
r = lngStart + Counter - 1
End If
' Copy cell values
Select Case Counter
Case 1
Plate_Width_Leg_1 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_1 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_1 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_1 = wshS.Cells(r, lngLVCol).Value
Case 2
Plate_Width_Leg_2 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_2 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_2 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_2 = wshS.Cells(r, lngLVCol).Value
Case 3
Plate_Width_Leg_3 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_3 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_3 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_3 = wshS.Cells(r, lngLVCol).Value
Case 4
Plate_Width_Leg_4 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_4 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_4 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_4 = wshS.Cells(r, lngLVCol).Value
Case 5
Plate_Width_Leg_5 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_5 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_5 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_5 = wshS.Cells(r, lngLVCol).Value
Case 6
Plate_Width_Leg_6 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_6 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_6 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_6 = wshS.Cells(r, lngLVCol).Value
Case 7
Plate_Width_Leg_7 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_7 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_7 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_7 = wshS.Cells(r, lngLVCol).Value
Case 8
Plate_Width_Leg_8 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_8 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_8 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_8 = wshS.Cells(r, lngLVCol).Value
Case 9
Plate_Width_Leg_9 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_9 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_9 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_9 = wshS.Cells(r, lngLVCol).Value
Case 10
Plate_Width_Leg_10 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_10 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_10 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_10 = wshS.Cells(r, lngLVCol).Value
Case 11
Plate_Width_Leg_11 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_11 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_11 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_11 = wshS.Cells(r, lngLVCol).Value
Case 12
Plate_Width_Leg_12 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_12 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_12 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_12 = wshS.Cells(r, lngLVCol).Value
Case 13
Plate_Width_Leg_13 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_13 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_13 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_13 = wshS.Cells(r, lngLVCol).Value
Case 14
Plate_Width_Leg_14 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_14 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_14 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_14 = wshS.Cells(r, lngLVCol).Value
Case 15
Plate_Width_Leg_15 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_15 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_15 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_15 = wshS.Cells(r, lngLVCol).Value
Case 16
Plate_Width_Leg_16 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_16 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_16 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_16 = wshS.Cells(r, lngLVCol).Value
Case 17
Plate_Width_Leg_17 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_17 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_17 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_17 = wshS.Cells(r, lngLVCol).Value
Case 18
Plate_Width_Leg_18 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_18 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_18 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_18 = wshS.Cells(r, lngLVCol).Value
Case 19
Plate_Width_Leg_19 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_19 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_19 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_19 = wshS.Cells(r, lngLVCol).Value
Case 20
Plate_Width_Leg_20 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_20 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_20 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_20 = wshS.Cells(r, lngLVCol).Value
Case 21
Plate_Width_Leg_21 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_21 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_21 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_21 = wshS.Cells(r, lngLVCol).Value
Case 22
Plate_Width_Leg_22 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_22 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_22 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_22 = wshS.Cells(r, lngLVCol).Value
Case 23
Plate_Width_Leg_23 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_23 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_23 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_23 = wshS.Cells(r, lngLVCol).Value
Case 24
Plate_Width_Leg_24 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_24 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_24 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_24 = wshS.Cells(r, lngLVCol).Value
Case 25
Plate_Width_Leg_25 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_25 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_25 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_25 = wshS.Cells(r, lngLVCol).Value
Case 26
Plate_Width_Leg_26 = Val(wshS.Cells(r, lngStartCol).Value)
Plate_Width_Yoke_26 = wshS.Cells(r, lngStartCol + 1).Value
HV_Step_Size_26 = wshS.Cells(r, lngHVCol).Value
LV_Step_Size_26 = wshS.Cells(r, lngLVCol).Value
End Select
Next Counter
' Optional: close source workbook
wbkS.Close(False)
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
Gents, the latest code works in iLogic and does exactly what I need and does exactly the same as the original VBA code, for info, this code is actually automating a 3d model, when I select one design spec which is a parameter, the 3d model updates.
Thanks very much for your help.
Thanks very much for your help.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Lounger
- Posts: 31
- Joined: 10 Dec 2013, 22:25
Re: Using VB.NET iLogic code to get data from excel document
Hans, it would be helpful at some point if you could put a brief description line by line to explain what's happening, I understand some of it but not all, just want to learn as much as I can.
Thanks
hmk999
Thanks
hmk999