title: Excel VBA error End with with out with?
body: <p>Hi friends i am working on export excel rows to Sql Server 2008 Table
   in that way i am checking the row already exist in table or not</p>
  
  <p>my table has </p>
  
  <p>sap_code
  depot
  size
  entry_date</p>
  
  <p>if table exist that record skip that row and check next row of excel with table</p>
  
  <p>here goes my working code</p>
  
  <pre class="lang-vb prettyprint-override"><code>' ===== Export Using ADO =====
  
  Function ExportRangeToSQL(ByVal sourceRange As Range, _
      ByVal conString As String, ByVal table As String) As Integer
  
      On Error Resume Next
  
      ' Object type and CreateObject function are used instead of ADODB.Connection,
      ' ADODB.Command for late binding without reference to
      ' Microsoft ActiveX Data Objects 2.x Library
  
      ' ADO API Reference
      ' http://msdn.microsoft.com/en-us/library/ms678086(v=VS.85).aspx
  
      ' Dim con As ADODB.Connection
      Dim con As Object
      Set con = CreateObject("ADODB.Connection")
  
      con.ConnectionString = conString
      con.Open
  
      ' Dim cmd As ADODB.Command
      Dim cmd As Object
      Set cmd = CreateObject("ADODB.Command")
  
      cmd.CommandType = 1             ' adCmdText
  
         ' Dim rst As ADODB.Recordset
      Dim rst As Object
      Set rst = CreateObject("ADODB.Recordset")
  
      With rst
          Set .ActiveConnection = con
          .Source = "SELECT * FROM " &amp; table
          .CursorLocation = 3         ' adUseClient
          .LockType = 4               ' adLockBatchOptimistic
           .CursorType = 1             ' adOpenKeyset
          .CursorType = 0             ' adOpenForwardOnly
          .Open
  
          ' Do While Not .EOF
          '    .MoveNext
          ' Loop
  
          ' Column Mappings
  
          Dim tableFields(100) As Integer
          Dim rangeFields(100) As Integer
  
          Dim exportFieldsCount As Integer
          exportFieldsCount = 0
  
          Dim col As Integer
          Dim index As Integer
  
          For col = 1 To .Fields.Count - 1
              index = Application.Match(.Fields(col).Name, sourceRange.Rows(1), 0)
              If index &gt; 0 Then
                  exportFieldsCount = exportFieldsCount + 1
                  tableFields(exportFieldsCount) = col
                  rangeFields(exportFieldsCount) = index
              End If
          Next
  
          If exportFieldsCount = 0 Then
              ExportRangeToSQL = 1
              Exit Function
          End If
  
          ' Fast read of Excel range values to an array
          ' for further fast work with the array
  
          Dim arr As Variant
          arr = sourceRange.Value
  
          ' Column names should be equal
          ' For col = 1 To exportFieldsCount
          '     Debug.Print .Fields(tableFields(col)).Name &amp; " = " &amp; arr(1, rangeFields(col))
          ' Next
  
          ' The range data transfer to the Recordset
  
          Dim row As Long
          Dim rowCount As Long
          rowCount = UBound(arr, 1)
  
  
          Dim val As Variant
  
          For row = 2 To rowCount
  
          ' Testing the Ledger data to insert
          Dim qu As String
          Dim br, de, si, da As String
         br = arr(row, rangeFields(1))  ' sap_code from excel
         de = arr(row, rangeFields(2)) ' depot from excel
         si = arr(row, rangeFields(3)) ' size from excel
         da = arr(row, rangeFields(5)) ' entry_date from excel
  
       Set con = CreateObject("ADODB.Connection")
  
      con.ConnectionString = conString
      con.Open
  
  
        Dim rstTest As ADODB.Recordset
        Set rstTest = New ADODB.Recordset
        With rstTest
         .CursorLocation = adUseClient
         .Open "select TOP 1 sap_code, depot, size, entry_date from openstock where " + "sap_code='" + br + "' and depot='" + de + "' and size='" + si + "' and entry_date='" + da + "' ORDER BY id DESC", con, adOpenStatic, adLockBatchOptimistic, adCmdText
    MsgBox "SAP_CODE" &amp; br &amp; "Depot" &amp; de &amp; "Size" &amp; si &amp; "entry_date" &amp; da &amp; "Duplicate Entry Not Entered into Database"
           If br = rstTest.Fields("sap_code").Value And _
              de = rstTest.Fields("depot").Value And _
              si = rstTest.Fields("size").Value And _
              da = rstTest.Fields("entry_date").Value Then
  
  
              Else
  
        End With  **NOte:  Error showing here as End With with out With**
              .AddNew
              For col = 1 To exportFieldsCount
                  val = arr(row, rangeFields(col))
                  If IsEmpty(val) Then
                  Else
                      .Fields(tableFields(col)) = val
                  End If
              Next
              End If
          Next   **NOte: Problem showing here as Next with out FOR**
  
          .UpdateBatch
  
      End With
  
      rst.Close
      Set rst = Nothing
  
  
      con.Close
      Set con = Nothing
  
      ExportRangeToSQL = 0
  
  End Function
  </code></pre>