Loop through range and insert values into SQL Table

Question

A set of data in Excel looking like this:

Test1   12345678    1906    John    GY  DFS H1C Y
Test2   12345678    1806    Jack    GY  GQ  H1C Y
Test3   12345678    1706    Kate    GY  GQ  H1C Y
Test4   12345678    1606    Sawyer  GY  GQ  H1C 

The very last column is to check if data was already loaded to SQL Server.

I have written code to iterate through range and insert values into SQL Table. Within this code, it also checks that last column, if there is a Y, it should skip iteration and go to the next one..

It gives me an error, saying "Else without if".

Sub Connection()

    Dim Conn As ADODB.Connection
    Dim Command As ADODB.Command

    Set Conn = New ADODB.Connection
    Set Command = New ADODB.Command

    Dim i As Integer
    Dim rownumber As Integer

    rownumber = Sheets("Sheet1").Range("A1048576").End(xlUp).Row


    Conn.ConnectionString = "Provider=SQLOLEDB; Data Source=[Server];Initial Catalog=[DB];User ID=[user];Password=[Password]; Trusted_Connection=no"

    Conn.Open

    Command.ActiveConnection = Conn


    For i = 1 To rownumber 'rows

        If ActiveSheet.Cells(i, 8).Value = "Y" Then GoTo NextIteration

        Else

        Command.CommandText = "INSERT INTO [Database] (" & _
            "[Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7])" & _
            "VALUES (" & _
            "'" & ActiveSheet.Cells(i, 1).Value & "'," & _
            "'" & ActiveSheet.Cells(i, 2).Value & "'," & _
            "'" & ActiveSheet.Cells(i, 3).Value & "'," & _
            "'" & ActiveSheet.Cells(i, 4).Value & "'," & _
            "'" & ActiveSheet.Cells(i, 5).Value & "'," & _
            "'" & ActiveSheet.Cells(i, 6).Value & "'," & _
            "'" & ActiveSheet.Cells(i, 7).Value & "')"

        Command.Execute
        ActiveSheet.Cells(i, 8).Value = "Y"
        End If
    Next i


    Conn.Close

    Set Conn = Nothing

End Sub

I am really struggling to figure out where I went wrong. The code works perfectly fine without checking if "Y" is there...

I appreciate your help.

Answer 1

Try the following

1. Use Option Explicit at the top to check for variable declarations
2. Use Long not Integer to avoid potential overflow as you are working with numbers of rows which can exceed capacity of Integer
3. If statement needs to be broken over several lines to function with Else
4. Your GoTo referenced a label, NextIteration, which needed adding, You need to verify this is now in the correct place.

5. Avoid calling your sub connection and use something less ambiguous for the compiler

   Public Sub My_Connection()
   
       Dim Conn As ADODB.Connection
       Dim Command As ADODB.Command
   
       Set Conn = New ADODB.Connection
       Set Command = New ADODB.Command
   
       Dim i As Long
       Dim rownumber As Long
   
       rownumber = Worksheets("Sheet1").Range("A1048576").End(xlUp).Row
   
       Conn.ConnectionString = "Provider=SQLOLEDB; Data Source=[Server];Initial Catalog=[DB];User ID=[user];Password=[Password]; Trusted_Connection=no"
   
       Conn.Open        
       Command.ActiveConnection = Conn
   
       For i = 1 To rownumber                       'rows
   
           If ActiveSheet.Cells(i, 8).Value = "Y" Then
   
               GoTo NextIteration
   
           Else
   
               Command.CommandText = "INSERT INTO [Database] (" & _
                                     "[Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7])" & _
                                     "VALUES (" & _
                                     "'" & ActiveSheet.Cells(i, 1).Value & "'," & _
                                     "'" & ActiveSheet.Cells(i, 2).Value & "'," & _
                                     "'" & ActiveSheet.Cells(i, 3).Value & "'," & _
                                     "'" & ActiveSheet.Cells(i, 4).Value & "'," & _
                                     "'" & ActiveSheet.Cells(i, 5).Value & "'," & _
                                     "'" & ActiveSheet.Cells(i, 6).Value & "'," & _
                                     "'" & ActiveSheet.Cells(i, 7).Value & "')"
   
               Command.Execute
               ActiveSheet.Cells(i, 8).Value = "Y"
           End If
   
   NextIteration:
   
       Next i
   
       Conn.Close       
       Set Conn = Nothing
   
   End Sub
   

Answer 2

Add a new line after 'Then' on the first if statement or elses vba implicity closes the if statement.