Click here to Skip to main content
15,893,190 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
want to check the spread sheet 1st row to verify the data is correct. It's the header for each column. This seems to read past the cell it should.

What I have tried:

VB
private Sub SomeSubName(and so on)

 	Dim fp As String = SelectFile()
   	Dim ExcelData As DataTable
        Dim correctColumns = True

            ExcelData = PopulateDataTable(fp)
       
              
            If Not IsDBNull(ExcelData) Then
                Try
                    'loop over each column name and verify data is correct

                      For Each columnName In ExcelData.Columns
                   
                        Select Case columnName.ToString().ToLower()
                            Case "lastname"
                                correctColumns = True
                            Case "firstname"
                                correctColumns = True
                            Case "mi"
                                correctColumns = True
                            Case "suffix"
                                correctColumns = True
                            Case "street"
                                correctColumns = True
                            Case "city"
                                correctColumns = True
                            Case "state"
                                correctColumns = True
                            Case "zip"
                                correctColumns = True
                            Case "phone"
                                correctColumns = True
                            Case "fax"
                                correctColumns = True
                        End Select
                    Next
Posted
Updated 30-Jun-17 4:49am
v2
Comments
Richard MacCutchan 16-Jun-17 10:45am    
You have not tested for the end of the columns.
Member 13263454 16-Jun-17 11:24am    
I am not sure how to do that. I can't find the right thing on google.
Richard MacCutchan 16-Jun-17 11:36am    
Check whether it's null.
CHill60 18-Jun-17 16:34pm    
How does PopulateDataTable() actually populate ExcelData? That is where the problem is but you haven't shared the code.
Your logic is also completely wrong for working out if the columns are correct - correctColumns can never be false with your current code.

1 solution

You could use the below
dim LastColumn as long=0
LastColumn=Sheet1.Cells.Range("A1").End(xlToRight).Column

for j=1 to LastColumn
MsgBox(Sheet1.Cells(j, i).Value)
.....
....
...
next

This method would help in the below scenario
If you have data till Column Z and after that you have deleted 3 columns data
But ExcelData.Columns would still loop till Z, wherein it should loop only upto W.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900