Transfer data from Excel to SQL Table

[abduvali]

Hi All,


I need some help from you guys to sort this issue out:
I'm trying to transfer data from my excel sheet to my SQL table using VBA with the loop in it which gets triggered by hitting SAVE button, my sheet looks as follows:
[TABLE="width: 783"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Day
[/TD]
[TD]Name
[/TD]
[TD]Day
[/TD]
[TD]Name
[/TD]
[/TR]
[TR]
[TD]02/01/2017
[/TD]
[TD][/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]03/01/2017
[/TD]
[TD]P
[/TD]
[TD]ES
[/TD]
[TD]P
[/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]04/01/2017
[/TD]
[TD][/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]05/01/2017
[/TD]
[TD][/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]06/01/2017
[/TD]
[TD][/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]07/01/2017
[/TD]
[TD]S
[/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]08/01/2017
[/TD]
[TD][/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]09/01/2017
[/TD]
[TD][/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]10/01/2017
[/TD]
[TD]S
[/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]11/01/2017
[/TD]
[TD]S
[/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]12/01/2017
[/TD]
[TD]0
[/TD]
[TD]ES
[/TD]
[TD]0
[/TD]
[TD]TMS <-- last row
[/TD]
[/TR]
[TR]
[TD]13/01/2017
[/TD]
[TD]P
[/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]14/01/2017
[/TD]
[TD][/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD]15/01/2017
[/TD]
[TD][/TD]
[TD]ES
[/TD]
[TD][/TD]
[TD]TMS
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

Idea is to consolidate the following under just 3 Columns in my SQL table : fact.Holiday which consists of following columns: DATE, DAY and NAME

In order to transfer the data I have added a VBA code with the loop as I don't know how many names will be there so it has to loop until last row in a **** and then copy everything from the next column until its blank the following code been added under ThisWorkbook:
************************************************************
Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim conn As New ADODB.Connection
Dim iRowNo, ColNo As Integer
'Dim sDate As Date, sDay As String, sName As String

With Sheets("Schedule")

'Open a connection to SQL Server
conn.Open "Provider=SQLOLEDB;Data Source=myTable;Initial Catalog=Harmony;Integrated Security=SSPI;"

conn.Execute "DELETE FROM fact.holiday"
'Skip the header row
iColNo = 2

'Loop until empty cell in CustomerId
Do Until .Cells(1, iColNo) = ""
iRowNo = 2
Do Until .Cells(iRowNo, 1) = ""
sDate = .Cells(iRowNo, 1)
sDay = .Cells(iRowNo, iColNo)
sName = .Cells(iRowNo, iColNo + 1)

'Generate and execute sql statement to import the excel rows to SQL Server table
conn.Execute "insert into harmony.fact.holiday ([Date],[Day],[Name]) values ('" & sDate & "', '" & sDay & "', '" & sName & "')"
iRowNo = iRowNo + 1

Loop
iColNo = iColNo + 2
'MsgBox iColNo
'MsgBox .Cells(iRowNo, 1)

Loop
MsgBox "Transfer completed"
conn.Close
Set conn = Nothing

End With

End Sub
*****************************************************************

The problem that I have is that it does not transfer beyond 12 January 2017 ( seems like VBA treating 12 as last month of the year and just stops there, but if I change my dates and instead of 13 January I will have any date bellow 12 it will work just fine so don't know where i'm getting it wrong =o? as it stands it transfers only the following:

Date Day Name
2017-02-01 0 ES
2017-03-01 P ES
2017-04-01 0 ES
2017-05-01 0 ES
2017-06-01 0 ES
2017-07-01 S ES
2017-08-01 0 ES
2017-09-01 0 ES
2017-10-01 S ES
2017-11-01 S ES
2017-12-01 0 ES <-----this where it stops



I'm also getting an error in excel when I'm executing the following code

Any help or advice very much appreciated.


Thank you


Ali

 

[abduvali]

VBA error: CONVERSIOT FAILEDWHEN CONVERTING DATE AND/OR TIME FROMCHARACHTER STRING