Help Me import Excel spreadsheet

[zaza123]

I need help to create a table so, when i import data from microsoft excel, the data will go to the table on the form which is similar to the table in microsoft excel. Then from the form, if column 1is equal to column 2, messagebox = (NE), ELSE Messagebox = (E).

 

[colind2299]

Its a lot easier to move data from a database (SQL) to VB than it is from and excel file to VB. Does it have to be an excel file?

 

[zaza123]

It would be great if its an excel files cause im trying see if its possible,

 

[zaza123]

Here a demo of the file of what ive done so far
thx if there is an error in my code or u can make it better pl help me

 

[colind2299]

Here's how to get data from Excel, make sure to include the imports*

Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop


Private Sub AddData()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

oXL = CreateObject("Excel.Application")
oXL.Visible = False
oXL.DisplayAlerts = False
oWB = oXL.Workbooks.Open(MyFilePathString, Notify:=False, [ReadOnly]:=False)
oSheet = oWB.Worksheets(1) 'opens the first sheet



Dim a As UInt16 = 0 ' a counts ROWS
Dim b As UInt16 = 0 'b counts COLUMNS
Dim Names As New ArrayList
'Now I look for a cell with text "Staff"
For a = 1 To 10 Step 1
For b = 1 To 10 Step 1
If TypeOf (oSheet.Cells(a, b).Value) Is String Then
If oSheet.Cells(a, b).Value = "Staff" Then
Exit For
End If
End If
Next b
If oSheet.Cells(a, b).Value = "Staff" Then
Exit For
Else : b = 1
End If
Next a
If oSheet.Cells(a, b).Value <> "Staff" Then
GoTo ErrorFindingValues
Else : a = a + 1
End If

'Now I add the names in that row to an ArrayList
Dim i As Integer = b + 1
While oSheet.Cells(a, i).Value <> "" Or oSheet.Cells(a, (i + 1)).Value <> "" 'checks for two consecutive blank cells in a row before ending loop
If TypeOf (oSheet.Cells(a, i).Value) Is String And oSheet.Cells(a, i).Value <> "" Then
Names.Add(Trim(oSheet.Cells(a, i).Value))
End If
i = i + 1
If i = 1000 Then GoTo ErrorFindingValues 'max at 1000 columns
End While


oWB.Close(SaveChanges:=False)
oSheet = Nothing
oWB = Nothing
oXL.Quit()
oXL = Nothing

End Sub

 

[colind2299]

Here's a simple example of adding data from Excel cells to a variable (arraylist):

Dim MyData As New ArrayList

For a = 1 To 20 Step 1
For b = 1 To 20 Step 1
If IsNumeric(oSheet.Cells(a, b).Value) = True Then
MyData.Add(oSheet.Cells(a, b).Value)
End If
Next b
Next a