Posts by troy79

troy79 · Oct 8th 2022

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

Hi.

I have a VBA macro that I have been using to update a roster with planned and unplanned leave.

What I was hoping to do was to have the ability within the same code or a separate code to:

- Delete requests that are no longer required

or

- Change the type of planned or unplanned leave

Here is the script that I use to add planned and unplanned leave, any help in making the above changes I would really appreciate.

Code

Private Sub cbbAdd_Click()


answer = MsgBox("Are you sure you want make the changes?", vbYesNo + vbQuestion, "Add Record")
If answer = vbYes Then


Dim LeaveDate As Date
LeaveDate = txtLeaveStart.Text
Dim LeaveEnd As Date
LeaveEnd = txtLeaveEnd.Text
Dim FirstName As String
FirstName = FirstNameCombo.Text
Dim LeaveType As String
LeaveType = LeaveTypeCombo.Text
Dim DateAdded As Date
Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets("Leave")
Set tbl = wsh.ListObjects("tblEmployees")
Dim lRow As ListRow
For LeaveDate = CDate(txtLeaveStart.Text) To CDate(txtLeaveEnd.Text)


Set lRow = tbl.ListRows.Add
With lRow
.Range(1) = LeaveDate
.Range(2) = FirstName
.Range(3) = LeaveType
.Range(5) = Now()


End With
Next
End If


MsgBox "Complete"


End Sub

Display More

troy79 · Dec 27th 2019

Hi.

I currently have a VBA Script that will create new records within a SQL table directly from excel.
What I was hoping if there was a way to modify the below so it can change from a create to a modify/update of exisitng records in the SQL table.
The unique key will be PP_REFERNCE in all cases
So for example what the front end user will do is populate the PP_REFERENCE ID in cell A2 and then update the cells that they want changed in the SQL table.

PP_REFERENCE DIRECTOR_NAME DIRECTOR_STATUS
1234 DAVE APPROVED
4567 MARK PENDING

So what ever was in DIRECTOR_NAME before will now be replaced by the above from excel in the existing SQL table against the PP_REFERENCE

Code

Sub Button1_Click()



Dim conn As New ADODB.Connection

Dim iRowNo As Integer

Dim sPP_REFERENCE, sDATE_RECEIVED, sLOC_ID, sROLLOUT_REGION, sDISCONNECTION_DATE, sADDRESS, sADBOR, sBSA_STATUS, sSERVICE_CLASS, sRECONNECTION_REASON, sPRODUCT_TYPE, sDIRECTOR_NAME, sDIRECTOR_STATUS, sNBN_TRANS_FNN, sNBN_TRANS_DATE, sRECONNECTED_PAIR, sOLD_PATH_ID, sNEW_PATH_ID, sTLS_ID, sCOMPLETION_DATE, sCOMMENTS, sASSET_TRANSFERRED, sSTATUS As String



With Sheets("Sheet1")



'Open a connection to SQL Server

conn.Open "Provider=SQLOLEDB;Data Source=W00000PC0U35U3\SQLEXPRESS;Initial Catalog=ROC;Integrated Security=SSPI;"



'Skip the header row

iRowNo = 2



'Loop until empty cell in PP_REFERENCE

Do Until .Cells(iRowNo, 1) = ""

sPP_REFERENCE = .Cells(iRowNo, 1)

sDATE_RECEIVED = .Cells(iRowNo, 2)

sLOC_ID = .Cells(iRowNo, 3)

sROLLOUT_REGION = .Cells(iRowNo, 4)

sDISCONNECTION_DATE = .Cells(iRowNo, 5)

sADDRESS = .Cells(iRowNo, 6)

sADBOR = .Cells(iRowNo, 7)

sBSA_STATUS = .Cells(iRowNo, 8)

sSERVICE_CLASS = .Cells(iRowNo, 9)

sRECONNECTION_REASON = .Cells(iRowNo, 10)

sPRODUCT_TYPE = .Cells(iRowNo, 11)

sDIRECTOR_NAME = .Cells(iRowNo, 12)

sDIRECTOR_STATUS = .Cells(iRowNo, 13)

sNBN_TRANS_FNN = .Cells(iRowNo, 14)

sNBN_TRANS_DATE = .Cells(iRowNo, 15)

sRECONNECTED_PAIR = .Cells(iRowNo, 16)

sOLD_PATH_ID = .Cells(iRowNo, 17)

sNEW_PATH_ID = .Cells(iRowNo, 18)

sTLS_ID = .Cells(iRowNo, 19)

sCOMPLETION_DATE = .Cells(iRowNo, 20)

sCOMMENTS = .Cells(iRowNo, 21)

sASSET_TRANSFERRED = .Cells(iRowNo, 22)

sSTATUS = .Cells(iRowNo, 23)



'Generate and execute sql statement to import the excel rows to SQL Server table

conn.Execute "insert into dbo.ROCS (PP_REFERENCE, DATE_RECEIVED, LOC_ID, ROLLOUT_REGION, DISCONNECTION_DATE, ADDRESS, ADBOR, BSA_STATUS, SERVICE_CLASS, RECONNECTION_REASON, PRODUCT_TYPE, DIRECTOR_NAME, DIRECTOR_STATUS, NBN_TRANS_FNN, NBN_TRANS_DATE, RECONNECTED_PAIR, OLD_PATH_ID, NEW_PATH_ID, TLS_ID, COMPLETION_DATE, COMMENTS, ASSET_TRANSFERRED, STATUS) values ('" & sPEGA_REFERENCE & "', '" & sDATE_RECEIVED & "', '" & sLOC_ID & "', '" & sROLLOUT_REGION & "', '" & sDISCONNECTION_DATE & "', '" & sADDRESS & "', '" & sADBOR & "', '" & sBSA_STATUS & "', '" & sSERVICE_CLASS & "', '" & sRECONNECTION_REASON & "', '" & sPRODUCT_TYPE & "', '" & sDIRECTOR_NAME & "', '" & sDIRECTOR_STATUS & "', '" & sNBN_TRANS_FNN & "', '" & sNBN_TRANS_DATE & "', '" & sRECONNECTED_PAIR & "', '" & sOLD_PATH_ID & "', '" & sNEW_PATH_ID & "', '" & sTLS_ID & "', '" & sCOMPLETION_DATE & "', '" & sCOMMENTS & "', '" & sASSET_TRANSFERRED & "', '" & sSTATUS & "')"



iRowNo = iRowNo + 1

Loop



MsgBox "PPS imported."



conn.Close

Set conn = Nothing



End With



End Sub

Display More

Posts by troy79

Update my existing macro to be able to update and delete

VBA Update from Excel to SQL Table Contents.