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

hmk999 · Post by **hmk999** » 22 Jan 2014, 00:12

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

Post by **Rudi** » 22 Jan 2014, 04:35

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

Hi 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

OR

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

These are just "shots in the dark"

hmk999 · Post by **hmk999** » 22 Jan 2014, 19:33

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

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

Post by **HansV** » 22 Jan 2014, 22:16

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.

hmk999 · Post by **hmk999** » 23 Jan 2014, 19:07

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.

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

Post by **HansV** » 23 Jan 2014, 21:11

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:

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)

hmk999 · Post by **hmk999** » 23 Jan 2014, 21:46

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

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)

Post by **HansV** » 23 Jan 2014, 21:53

What happens if you change all occurrences of i (as a whole word) to something else, e.g. to Counter ?

hmk999 · Post by **hmk999** » 23 Jan 2014, 22:07

Replace them all but states the same message,

Error on Line 33 : Variable 'Counter' hides a variable in an enclosing block.

Post by **HansV** » 23 Jan 2014, 22:13

I'm afraid my lack of knowledge of iLogic and VB.NET shows. I am very sorry, but I can't solve your problem.

hmk999 · Post by **hmk999** » 23 Jan 2014, 22:16

I will look into this with iLogic rules and let you know how I get on.

Thanks for your help
Regards
hmk999

hmk999 · Post by **hmk999** » 23 Jan 2014, 22:56

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

Post by **HansV** » 23 Jan 2014, 22:59

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!

hmk999 · Post by **hmk999** » 23 Jan 2014, 23:03

OK thanks Hans

Post by **Rudi** » 24 Jan 2014, 05:30

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.

Just a

suggestion....
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

hmk999 · Post by **hmk999** » 24 Jan 2014, 22:39

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

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)

Post by **HansV** » 24 Jan 2014, 22:44

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)

hmk999 · Post by **hmk999** » 24 Jan 2014, 23:15

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.

Post by **HansV** » 24 Jan 2014, 23:21

Great to hear that!

hmk999 · Post by **hmk999** » 24 Jan 2014, 23:33

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

Eileen's Lounge

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

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

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document

Re: Using VB.NET iLogic code to get data from excel document