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.

Ok @QHarr , I done that, still does not work. May I ask why though? — Oday Salim
– Oday Salim, Commented Jun 3, 2018 at 12:42

QHarr · Accepted Answer · 2018-06-03 12:48:31Z

Try the following

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

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

Kenta Goto · Accepted Answer · 2018-06-03 12:29:27Z

0

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

answered Jun 3, 2018 at 12:29

Kenta Goto

2953 silver badges19 bronze badges

1 Comment

Kenta Goto Over a year ago

Sorry. Why not leave the content under the if statement blank instead of GoTo NextIteration?

Collectives™ on Stack Overflow

Loop through range and insert values into SQL Table

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related