Similar coding required for Visual Basic .NET 2005

Similar coding required for Visual Basic .NET 2005

• N B

  Not sure if I understand what you need to do - did you mean having visual basic accessing an excel spreadsheet, and doing a similar operation as that formula, or a more generic visual basic oriented way of finding elements in arrays and using those to index another, two dimensional array

  A few pointers:

  In .net, arrays have an indexof property that returns the index of the element in the array, but this only works for one dimensional arrays - if you're trying to index 2 dimensional arrays (which is what I believe cells in the excel api show up as) you'll probably need to define your own lookup function - consisting of a loop and comparing until an element is found.

  Index could probably just be replaced by indexing whatever array c13:k24 represents, shifted to the start positions (if c= 3rd element, arr(3 + index1, 12 + index2).

  
  
  

• mliesmons

  Caveat: I'm not an expert in office, this code can easily have quite a few errors/bad practices. It does seem to work though :)

  Second thing is, I didn't create a UI to do this - I just set two variables to sample values for sizes.

  Third thing, you'll need a reference to Microsoft.Office.Interop.Excel.dll

  And now, the sample:
  

  Module Module1

  Sub Main()

   

  'My test excel spreadsheet has these values:

  ' A B C D E F

  '1 Width

  '2 S M L XL

  '3 height S 1 2 3 4

  '4 M 5 6 7 8

  '5 L 9 10 11 12

  '6 XL 13 14 15 16

  'needed references ' Microsoft.Office.Interop.Excel.dll

  Dim app As New Microsoft.Office.Interop.Excel.Application

  Dim workbook As Microsoft.Office.Interop.Excel.Workbook = app.Workbooks.Open("e:\temp\x.xls")

  Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets()("sheet1")

  'Given a height size and a width size, find the price

  'These would probably be entered in a windows form. These values are for test purposes

  Dim Width As String = "M"

  Dim Height As String = "L"

  Dim widthsizes As Microsoft.Office.Interop.Excel.Range

  Dim heightsizes As Microsoft.Office.Interop.Excel.Range

  widthsizes = worksheet.Range("C2", "F2")

  heightsizes = worksheet.Range("B2", "B6")

   

  Dim WidthIndex As Integer = widthsizes.Find(Width).Column

  Dim heightIndex As Integer = heightsizes.Find(Height).Row

  Console.WriteLine(worksheet.Cells(heightIndex, WidthIndex).value)

  app.Quit()

  End Sub

  End Module

  
  
  

• Mark Gillis

  But you do want to keep the data in the excel spreadsheet then, just move the logic to VB
  
  

• Mark_Davies

  Hi Alex,

  Your first line is exactly what I am trying to say. I would like visual basic to access an excel spreadsheet and perform a similar operation as that of the Excel formula.

  This is the situation. I have an Excel sheet with pricing information in it, this is within the index portion. The rows represent a width size and the columns represent a height size. Depending on these two dimensions, there is a matching price. This price is then reflected in the cell with the above Excel formula. I would prefer Visual Basic to perform this task.

  Thanks in advance,

  Chris

  
  
  

• jklegseth

  Morning Alex,

  That is correct, I want to keep the information in Excel but the formula INDEX(C13:K24,MATCH(A2,B13:B24,0),MATCH(B2,C13:K13,0) is what I would prefer to convert into VB logic.

  Thanks in advance,

  Chris

  
  
  

• TimFröglich

  Hello Alex,

  Thanks for that, removed the line "Module Module1" as this through VB.Net off and the rest of the code produced the results I was looking for.

  Thanks once again, excellent solution.

  
  
  
• Similar coding required for Visual Basic .NET 2005