VBA Excel - Select a value from one cell in Excel to update a SQL table


Joined
Aug 6, 2021
Messages
2
Reaction score
0
I am working on a project to track profit and loss from a billing standpoint. We have a per piece cost that I am using to track the expenses. The end user would like to be able to change the per piece cost from time to time prior to update the table. I am trying to us a macro to take a value from sheet3 cell B2 in the Excel worksheet in order to update a table in SQL server. I am missing something as when I run the macro it does not update the table. Could anyone tell me what I am missing. This is my first attempt at using VBA so I'm stuck for the moment.

Sub UpdateSQLData()

Dim cnn As ADODB.Connection

Dim MySQL As String

Worksheets("Sheet3").Visible = True
Worksheets("Sheet3").Select

Set cnn = New ADODB.Connection



cnn.Open "Provider=SQLOLEDB.1;Initial Catalog=database;Data Source=server;UID=uid;PWD=pwd;"


Worksheets("Sheet3").Activate
For i = 2 To Range("B2").Select
MySQL = "UPDATE opi_bill_cost " & _
"SET Cost ='" & Range("B2").Value & "', Outcome = '"


cnn.Execute MySQL
Next i

cnn.Close

Set cnn = Nothing

End Sub
 
Ad

Advertisements

Joined
Aug 6, 2021
Messages
2
Reaction score
0
I figured this out. I'm not sure how to delete a thread on here so thought I'd let you know.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top