title: Export Excel to SQL using VBA
body: <p>Was hoping if someone could help re-script this for me. I have asked this question before but did not receive the help needed.</p>
  <p>I am trying to create a excel file with a macro attached to a button that will export the data to SQL.</p>
  <p>While creating the macro in Visual Basic for Applications my code is as follows:</p>
  <pre class="lang-vb prettyprint-override"><code>Sub Button1_Click()
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim sRecordedPeriod, sEventDate, sID, sDeptCode, sOpCode, sStartTime, sFinishTime, sUnits  As String
  
    With Sheets(&quot;Sheet1&quot;)
      'Open a connection to SQL Server
      conn.Open &quot;Provider=SQLOLEDB;Data Source=db1\db1;Initial Catalog=ProdTrack;Integrated Security=SSPI;&quot;
      'Skip the header row
      iRowNo = 2
      'Loop until empty cell in FirstName
      Do Until .Cells(iRowNo, 1) = &quot;&quot;
          sRecordedPeriod = .Cells(iRowNo, 1)
          sEventDate = .Cells(iRowNo, 2)
          sID = .Cells(iRowNo, 3)
          sDeptCode = .Cells(iRowNo, 4)
          sOpCode = .Cells(iRowNo, 5)
          sStartTime = .Cells(iRowNo, 6)
          sFinishTime = .Cells(iRowNo, 7)
          sUnits = .Cells(iRowNo, 8)
          'Generate and execute sql statement to import the excel rows to SQL Server table
          conn.Execute &quot;insert into dbo.TimeLog (RecordedPeriod, EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) values ('&quot; &amp; sRecordedPeriod &amp; &quot;', '&quot; &amp; sEventDate &amp; &quot;', '&quot; &amp; sID &amp; &quot;', '&quot; &amp; sDeptCode &amp; &quot;', '&quot; &amp; sOpCode &amp; &quot;', '&quot; &amp; sStartTime &amp; &quot;', '&quot; &amp; sFinishTime &amp; &quot;', '&quot; &amp; sUnits &amp; &quot;')&quot;
          iRowNo = iRowNo + 1
      Loop
          
      MsgBox &quot;Data Successfully Exported.&quot;
      conn.Close
      Set conn = Nothing
    End With
  End Sub
  </code></pre>
  <p>I receive this error message when exporting.</p>
  <blockquote>
  <p>Run-time error '2147217913 (80040e07)':</p>
  <p>Conversion failed when converting date and/or time from character string.</p>
  </blockquote>
  <p>The table I am trying to export to in SQL looks like this. I don't think I am getting an error on the <code>EventTime</code> as the data type is <code>varchar(8)</code> not a date/time data type...</p>
  <p>My SQL table is as follows:</p>
  <pre class="lang-sql prettyprint-override"><code>    RecordedPeriod DATETIME NOT NULL DEFAULT (GETDATE()),
      EventDate (varchar(8), not null)
      ID (int, not null)
      DeptCode (varchar(2), not null)
      OpCode (varchar(2), not null)
      StartTime (time(0), not null)
      FinishTime (time(0), not null)
      Units (int, not null)
  </code></pre>
  <p>This is what my Excel table looks like:</p>
  <pre class="lang-none prettyprint-override"><code>RecordedPeriod  EventDate   ID      DeptCode    OpCode  StartTime   FinishTime  Units
  null             6/22/17    45318   DC          DC      8:00:00     8:15:00     250
  </code></pre>
  <p><a href="https://i.stack.imgur.com/AeTuf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AeTuf.png" alt="enter image description here" /></a></p>