Export Excel to SQL using VBA

Question

Was hoping if someone could help re-script this for me. I have asked this question before but did not receive the help needed.

I am trying to create a excel file with a macro attached to a button that will export the data to SQL.

While creating the macro in Visual Basic for Applications my code is as follows:

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("Sheet1")
    'Open a connection to SQL Server
    conn.Open "Provider=SQLOLEDB;Data Source=db1\db1;Initial Catalog=ProdTrack;Integrated Security=SSPI;"
    'Skip the header row
    iRowNo = 2
    'Loop until empty cell in FirstName
    Do Until .Cells(iRowNo, 1) = ""
        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 "insert into dbo.TimeLog (RecordedPeriod, EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) values ('" & sRecordedPeriod & "', '" & sEventDate & "', '" & sID & "', '" & sDeptCode & "', '" & sOpCode & "', '" & sStartTime & "', '" & sFinishTime & "', '" & sUnits & "')"
        iRowNo = iRowNo + 1
    Loop
        
    MsgBox "Data Successfully Exported."
    conn.Close
    Set conn = Nothing
  End With
End Sub

I receive this error message when exporting.

Run-time error '2147217913 (80040e07)':

Conversion failed when converting date and/or time from character string.

The table I am trying to export to in SQL looks like this. I don't think I am getting an error on the EventTime as the data type is varchar(8) not a date/time data type...

My SQL table is as follows:

    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)

This is what my Excel table looks like:

RecordedPeriod  EventDate   ID      DeptCode    OpCode  StartTime   FinishTime  Units
null             6/22/17    45318   DC          DC      8:00:00     8:15:00     250

We can know the problem, when you summit picture of the excell sheet. — Dy.Lee, Jun 23 '17 at 04:25
Try `#` for time datatype instead of `'`. Use [ADO Parameters](https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/append-and-createparameter-methods-example-vb). — BitAccesser, Jun 23 '17 at 05:05
@BitAccesser kind of new with vba...could you help with a re script? not sure where — DRUIDRUID, Jun 23 '17 at 05:06
I see that RecordedPeriod can't get NULL, but it is null, so escape `Nz(sRecordedPeriod, "1900-01-01")`. Try this first, then (if not fixed) just replace surounding quotes with `#` for all date/time types (RecordedPeriod, StartTime, FinishTime). — BitAccesser, Jun 23 '17 at 05:14
@BitAccesser I removed the RecordedPeriod just to check if that was the problem, nothing. So then I added the '#' surrounding StartTime and FinishTime. Now I show a Run-time error '-2147217900 Incorrect syntax near '.333333333333333'. — DRUIDRUID, Jun 23 '17 at 05:21
define the text after `conn.Execute` as a String (`Dim strSQL as String strSQL = "...text.. after conn" debug.print strSQL`) and show the resulting statement. — BitAccesser, Jun 23 '17 at 05:24
@BitAccesser getting compile errors, would you be able to script for me? please — DRUIDRUID, Jun 23 '17 at 05:29
`Dim strSQL as String (new line here) strSQL = "insert into dbo.TimeLog (RecordedPeriod, EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) values ('" & sRecordedPeriod & "', '" & sEventDate & "', '" & sID & "', '" & sDeptCode & "', '" & sOpCode & "', '" & sStartTime & "', '" & sFinishTime & "', '" & sUnits & "')" (new line here) debug.print strSQL`. — BitAccesser, Jun 23 '17 at 05:34
@BitAccesser OK so now I am getting the data successfully exported box show up but its actually not going to my db with the original error in the immediate box -2147217913. Conversion failed when converting date and/or time from character string. — DRUIDRUID, Jun 23 '17 at 05:48
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/147424/discussion-between-druidruid-and-bitaccesser). — DRUIDRUID, Jun 23 '17 at 05:52
In mssql, it recognise timevalue as text , so you have to cast text as datetime. — Dy.Lee, Jun 23 '17 at 18:21

score 2 · Answer 1 · answered Jun 23 '17 at 21:38

Here's the parameter approach:

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command

Dim strSQL As String

strSQL = "INSERT INTO dbo.TimeLog " & _
    "(RecordedPeriod, EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) " & _
"VALUES (?,?,?,?,?,?,?,?);"

Set conn = New ADODB.Connection
conn.Open ""Provider=SQLOLEDB;Data Source=db1\db1;Initial Catalog=ProdTrack;Integrated Security=SSPI;"
'Skip the header row
iRowNo = 2"

Set cmd = New ADODB.Command

cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL

iRowNo = 2

With Sheets("Sheet1")
  'Loop until empty cell in FirstName
  Do Until .Cells(iRowNo, 1) = ""

  cmd.Parameters.Append _
    cmd.CreateParameter("pRecordedPeriod", adDBTimeStamp, adParamInput, 8, .Cells(iRowNo, 1))
  cmd.Parameters.Append _
    cmd.CreateParameter("pEventDate", adVarChar, adParamInput, 8, .Cells(iRowNo, 2))
  cmd.Parameters.Append _
    cmd.CreateParameter("pID", adInteger, adParamInput, , .Cells(iRowNo, 3))
  cmd.Parameters.Append _
    cmd.CreateParameter("pDeptCode", adVarChar, adParamInput, 2, .Cells(iRowNo, 4))
  cmd.Parameters.Append _
    cmd.CreateParameter("pOpCode", adVarChar, adParamInput, 2, .Cells(iRowNo, 5))
  cmd.Parameters.Append _
    cmd.CreateParameter("pStartTime", adDBTime, adParamInput, 0, .Cells(iRowNo, 6))
  cmd.Parameters.Append _
    cmd.CreateParameter("pFinishTime", adDBTime, adParamInput, 0, .Cells(iRowNo, 7))
  cmd.Parameters.Append _
    cmd.CreateParameter("pUnits", adInteger, adParamInput, , .Cells(iRowNo, 8))

  cmd.Execute

  iRowNo = iRowNo + 1
  Loop
End With

conn.Close
Set conn = Nothing

the DataTypeEnum

But this is not nice to code, compared to a DAO parameter-query. No named parameters, five parameters to configure. This is for Stored Procedures? For CRUDthis is no fun.

This executes well, thanks....The only way I got it to work was by dropping the RecordedPeriod column. I want to insert NULL which should get me the current_timestamp in the db.......Is there any way we could do that? — DRUIDRUID, Jun 23 '17 at 23:10
getting another error code on the RecordedPeriod parameter.........Run-time error '3421'.....Application uses a value of the wrong type for the current operation.. — DRUIDRUID, Jun 23 '17 at 23:50
Hey, I suppose I can live without the RecordedPeriod column and can remove completely from my DB. With that being said, it seems like I can only add one row at a time. the goal would be to add several rows with one click of the button — DRUIDRUID, Jun 23 '17 at 23:58
Just remove the column name, the `pRecordedPeriod` parameter append and one `?,`. If you insert a row and don't provide a value for the column the default value is used (if there is any). Alternative: Insert the current datetime provided by `Now()`. — BitAccesser, Jun 24 '17 at 06:31
hey so I completely removed the RecordedPeriod column from sql and excel vba, I am running into another issue now, i can export one row at a time only, i want to be able to upload several rows at once, can you assist? — DRUIDRUID, Jul 06 '17 at 04:16
I just posted another question about how to delete instead of insert. Do you mind giving a hand with it? `Delete SQL rows from Excel` — DRUIDRUID, Aug 17 '17 at 23:26

score 0 · Answer 2 · answered Jun 23 '17 at 17:57

ADO to the rescue.

Dim cn As New ADODB.Connection

''You should probably change Activeworkbook.Fullname to the
''name of your workbook
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& ActiveWorkbook.FullName _
 & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

cn.Open strCon

s = "INSERT INTO [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable ( Col1, Col2, Col3, Col4 ) " _
& "SELECT  a.Col1, a.Col2, a.Col3, a.Col4 " _
& "FROM [Sheet2$] a " _
& "LEFT JOIN [ODBC;Description=TEST;DRIVER=SQL Server;" _
& "SERVER=Server;Trusted_Connection=Yes;" _
& "DATABASE=test].SomeTable b ON a.Col1 = b.Col1 " _
& "WHERE b.Col1 Is Null"
cn.Execute s

Also, check out these links.

https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm

http://tomaslind.net/2013/12/26/export-data-excel-to-sql-server/

I am actually new to vb, could you script the above showing where I need to put my credentials? ANd yes ive already looked at Tomaslind, I have commented on that site but they have not responded to me yet — DRUIDRUID, Jun 23 '17 at 19:38

Dy.Lee · Answer 3 · 2017-06-23T23:11:52.250

In mssql, it recognise value of datetime at Excel as text , so you have to cast text as datetime.

cast('8:00' as datetime)

apply to your code with upper cast method .

also cells(i,1) catch numeric value.

Your sEventDate is string, so sEventDate = .Cells(iRowNo, 2).Text

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("Sheet1")
    'Open a connection to SQL Server
    conn.Open "Provider=SQLOLEDB;Data Source=db1\db1;Initial Catalog=ProdTrack;Integrated Security=SSPI;"
    'Skip the header row
    iRowNo = 2
    'Loop until empty cell in FirstName
    Do Until .Cells(iRowNo, 1) = ""
        sRecordedPeriod = .Cells(iRowNo, 1)
        sEventDate = .Cells(iRowNo, 2).Text
        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 "insert into dbo.TimeLog (RecordedPeriod, EventDate, ID, DeptCode, Opcode, StartTime, FinishTime, Units) values ( '" & sRecordedPeriod & "' , '" & sEventDate & "'  , '" & sID & "', '" & sDeptCode & "', '" & sOpCode & "', cast('" & sStartTime & "' as datetime) , cast('" & sFinishTime & "' as datetime), '" & sUnits & "')"
        iRowNo = iRowNo + 1
    Loop

    MsgBox "Data Successfully Exported."
    conn.Close
    Set conn = Nothing
  End With
End Sub

hey, thanks for coming back. I tried this format, and I still receive a error... Conversion failed when converting date and/or time from character string..........I notice you want to cast EventDate as Datetime? It is only varchar in my sql DB (Version 2008R2) — DRUIDRUID, Jun 23 '17 at 19:37
@DRUIDRUID: I modified my Code that have several mistake code. — Dy.Lee, Jun 23 '17 at 23:13
Hey, still get the error code........Conversion failed when converting date and/or time from character string — DRUIDRUID, Jun 23 '17 at 23:52

score 0 · Answer 4 · answered Jun 23 '17 at 19:47

Here is another way to do it. Maybe this is easier to follow...

Add a reference to: Microsoft ActiveX Data Objects 2.8 Library

Sub Button_Click()
'TRUSTED CONNECTION
    On Error GoTo errH

    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strPath As String
    Dim intImportRow As Integer
    Dim strFirstName, strLastName As String

    Dim server, username, password, table, database As String


    With Sheets("Sheet1")

            server = .TextBox1.Text
            table = .TextBox4.Text
            database = .TextBox5.Text


            If con.State <> 1 Then

                con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                'con.Open

            End If
            'this is the TRUSTED connection string

            Set rs.ActiveConnection = con

            'delete all records first if checkbox checked
            If .CheckBox1 Then
                con.Execute "delete from tbl_demo"
            End If

            'set first row with records to import
            'you could also just loop thru a range if you want.
            intImportRow = 10

            Do Until .Cells(intImportRow, 1) = ""
                strFirstName = .Cells(intImportRow, 1)
                strLastName = .Cells(intImportRow, 2)

                'insert row into database
                con.Execute "insert into tbl_demo (firstname, lastname) values ('" & strFirstName & "', '" & strLastName & "')"

                intImportRow = intImportRow + 1
            Loop

            MsgBox "Done importing", vbInformation

            con.Close
            Set con = Nothing

    End With

Exit Sub

errH:
    MsgBox Err.Description
End Sub

Export Excel to SQL using VBA

4 Answers4

Linked