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:
<tbody>
</tbody>
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
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:
| Date | Day | Name | Day | Name |
| 02/01/2017 | ES | TMS | ||
| 03/01/2017 | P | ES | P | TMS |
| 04/01/2017 | ES | TMS | ||
| 05/01/2017 | ES | TMS | ||
| 06/01/2017 | ES | TMS | ||
| 07/01/2017 | S | ES | TMS | |
| 08/01/2017 | ES | TMS | ||
| 09/01/2017 | ES | TMS | ||
| 10/01/2017 | S | ES | TMS | |
| 11/01/2017 | S | ES | TMS | |
| 12/01/2017 | 0 | ES | 0 | TMS <-- last row |
| 13/01/2017 | P | ES | TMS | |
| 14/01/2017 | ES | TMS | ||
| 15/01/2017 | ES | TMS | ||
<tbody>
</tbody>
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
