• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 462
  • Last Modified:

Excell 2007 - SQL 2008R2

Hello,
I have the managers in different cities update an excel sheet, they send me the sheets daily, and I have to update my SQL database.

Is there a way they can use the excel sheet, link it to my server (ODBC or any other method), that they can update the Excel changes done to my SQL SERVER.
Please see attached what I have in mind.

My update code in SQL is simple.
Update Activeorders set WaybillNumber = test.Code where ActiveOrders.Orderno = test.OrderNo

Your help is appreciated.
Test.xls
0
W.E.B
Asked:
W.E.B
  • 17
  • 14
1 Solution
 
RyanProject Engineer, ElectricalCommented:
You could create a button to READ in the data from the server, effectively refreshing the list (or do that automatically when the spreadsheet is opened).

I presume OrderNo is the PK, so that column needs to be readonly.

Track the edits done to Col B:D, and indicate any changes in another column, say H.
So now if any rows are modified, the corresponding H column will have a 1 or true or something.

When they click update, you check for the rows with values in H, and do an update Query using the primary key A.

Then they can refresh to ensure all changes were done.
0
 
W.E.BAuthor Commented:
Hello,
I follow this logic, but I'm having problem setting this up.

Are you able to help.

Thanks
0
 
RyanProject Engineer, ElectricalCommented:
Do you have any of this coded?  I can't develop the queries for you, as I can't access the data.

The rest I can do though.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
W.E.BAuthor Commented:
There are only 2 queries to execute

Select * from Acttiveorders where accountnumber in (3333)
or
Select * from Acttiveorders where Ordernumber in (Excel Range A2:A5000) -- NOT sure if this is even possible.

Update Activeorders set WaybillNumber = test.Code where ActiveOrders.Orderno = test.OrderNo

I'm not sure if any other queries are required.

thanks
0
 
RyanProject Engineer, ElectricalCommented:
Here is most of the coding.  You need to provide the connection string.

I didn't test any of this code.

For those reading and don't want to open the file.
Module Code
Option Explicit

Dim sh As Sheets
Dim cnn As Connection

Public Sub RefreshData()
    Set sh = Sheets("Orders")
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Set cnn = New Connection 'Need to set the connection string to your database here
    cnn.Open
    
    sql = "SELECT * FROM Acttiveorders WHERE accountnumber in (3333)"
    'Delete old data
    sh.Range("A2:H65000").ClearContents
    
    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
End Sub

Public Sub UpdateData()
    Set sh = Sheets("Orders")

    Set cnn = New Connection 'Need to set the connection string to your database here
    cnn.Open
    Dim i As Integer
    
    'Find rows that have been modified
    For i = 2 To Range("A1").End(xlDown).Row
        If Cells(i, 8) = "Modified" Then
        UpdateRow (i)
        End If
    Next i
    cnn.Close
End Sub

Private Sub UpdateRow(rowIndex As Integer)
    Dim sql As String
    sql = "UPDATE Activeorders SET WaybillNumber = '" & sh.Cells(rowIndex, 4) & "' WHERE ActiveOrders.Orderno = " & sh.Cells(rowIndex, 1)
    
    Dim rs As New ADODB.Recordset
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    rs.Close
End Sub

Open in new window


Worksheet Code
Private Sub Worksheet_Change(ByVal Target As Range)
'Save that column was modified
If Target.Column = 4 Then
    Cells(Target.Row, 8) = "Modified"
End If

End Sub

Open in new window

Test.xls
0
 
W.E.BAuthor Commented:
thank you for your time and help.

I added my string

Set cnn = New Connection 'Provider=SQLOLEDB.1;Password=PASSWORDXXXX!;Persist Security Info=True;User ID=sa;Initial Catalog=CourierComplete;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False

When I hit Refresh:
error: Compile Error: Method or Data member not found
-----          sh.Range("A2:H65000").ClearContents

thanks
0
 
RyanProject Engineer, ElectricalCommented:
very first line should be
Dim sh As Worksheet
0
 
W.E.BAuthor Commented:
I get connection error

Run-time error:'-2147467259 (80004005)

cnn.Open
0
 
W.E.BAuthor Commented:
I get the same error when I run the refresh.
Run-time error:'-2147467259 (80004005)
cnn.Open

My connection to the server is working, I tested on some other sheets.

thanks
0
 
RyanProject Engineer, ElectricalCommented:
Tested it on other sheets? You used similar code? Same connection string?

I'm guessing either there's something wrong with the connection string.

The one you've posted won't work, as it's commented out, it needs to be a string (surrounded by double quotes).
0
 
W.E.BAuthor Commented:
I tested the connection with a different code.(not this code).

here is the connection string

    Set cnn = New Connection  "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
  cnn.Open

if I put double qoutes,I get error
Compile error
Expected: end of statment.

Thanks
0
 
RyanProject Engineer, ElectricalCommented:
Sorry, doing a lot from memory, and I'm usually programming in VB.NET, not VBA.

I don't know if it knows Connection is ADODB.Connection, so change line 4 to
Dim cnn As adodb.Connection

Set the connection via:

Set cnn = New adodb.Connection

cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
0
 
W.E.BAuthor Commented:
I appreciate your help,
I still get errors.
(Compile error: Method or data member not found)
Here is the code

Option Explicit

Dim sh As Sheets
Dim cnn As Connection

Public Sub RefreshData()
    Set sh = Sheets("Orders")
    '' Dim rs As New ADODB.Recordset
    Dim cnn As adodb.Connection
    Dim sql As String
    Set cnn = New adodb.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
    cnn.Open
   
    sql = "SELECT * FROM Acttiveorders WHERE accountnumber in (3333)"
    'Delete old data
    sh.Range("A2:H65000").ClearContents
   
    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
End Sub

Public Sub UpdateData()
    Set sh = Sheets("Orders")

    Set cnn = New adodb.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
    cnn.Open
   
    Dim i As Integer
   
    'Find rows that have been modified
    For i = 2 To Range("A1").End(xlDown).Row
        If Cells(i, 8) = "Modified" Then
        UpdateRow (i)
        End If
    Next i
    cnn.Close
End Sub

Private Sub UpdateRow(rowIndex As Integer)
    Dim sql As String
    sql = "UPDATE Activeorders SET WaybillNumber = '" & sh.Cells(rowIndex, 4) & "' WHERE ActiveOrders.Orderno = " & sh.Cells(rowIndex, 1)
   
    Dim rs As New adodb.Recordset
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    rs.Close
End Sub
0
 
RyanProject Engineer, ElectricalCommented:
dim sh as worksheet   (not sheets)

I don't know what line the error is on, but I'm guessing its sh.range, since range doesn't belong to sheets
0
 
W.E.BAuthor Commented:
I changed to
dim sh as worksheet  

now I get error

Compile error: Variable not Defined
 rs.Open sql, cnn, adOpenDynamic, adLockOptimistic

thanks
0
 
W.E.BAuthor Commented:
Hello,
I'm pulling my hair OUT, can someone please help.
Please see attached,
I'm able to get the data from SQL to the excel sheet.

I just can't figure out how to update back my SQL.

Update Activeorders set WaybillNumber = test.Code where ActiveOrders.Orderno = test.OrderNo

Thanks.
Test.xls
0
 
RyanProject Engineer, ElectricalCommented:
The code has changed quite a bit from what I supplied. I don't even see the Update Method that I started for you in there.

Need to use ADODB.Command instead of recordset to use an update query.  I mixed it up with editing values from a SELECT query.

     Dim cmd As ADODB.Command
     cmd.ActiveConnection = cnn
     cmd.CommandText
     cmd.Execute
0
 
W.E.BAuthor Commented:
Hello,
here is the code you supplied,

I still get errors.

Compile error: Variable not Defined
 rs.Open sql, cnn, adOpenDynamic, adLockOptimistic


Option Explicit

dim sh as worksheet
Dim cnn As Connection

Public Sub RefreshData()
    Set sh = Sheets("Orders")
    '' Dim rs As New ADODB.Recordset
    Dim cnn As adodb.Connection
    Dim sql As String
    Set cnn = New adodb.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
    cnn.Open
   
    sql = "SELECT * FROM Acttiveorders WHERE accountnumber in (3333)"
    'Delete old data
    sh.Range("A2:H65000").ClearContents
   
    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
End Sub

Public Sub UpdateData()
    Set sh = Sheets("Orders")

    Set cnn = New adodb.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
    cnn.Open
   
    Dim i As Integer
   
    'Find rows that have been modified
    For i = 2 To Range("A1").End(xlDown).Row
        If Cells(i, 8) = "Modified" Then
        UpdateRow (i)
        End If
    Next i
    cnn.Close
End Sub

Private Sub UpdateRow(rowIndex As Integer)
    Dim sql As String
    sql = "UPDATE Activeorders SET WaybillNumber = '" & sh.Cells(rowIndex, 4) & "' WHERE ActiveOrders.Orderno = " & sh.Cells(rowIndex, 1)
   
    Dim rs As New adodb.Recordset
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    rs.Close
End Sub
0
 
RyanProject Engineer, ElectricalCommented:
There are multiple corrections I have made that are not in this code.

Connection is not adodb.
rs in UpdateRow should be cmd.

The error is because you commented out the rs declaration in RefreshData
0
 
W.E.BAuthor Commented:
Hello,
ok, I got the connection going,
I had to change your connection code a tiny bit

    Set cnn = New adodb.Connection
    cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=Password!;Initial Catalog=Database;Data Source=Wassim-Laptop;"

Now, here is the issue.
it doesn't seem to be doing any updates in SQL,but rather it is doing the reverse.

it is UPDATING/replacing  Excel Columns (A,B,C,D), with the select Statement results. from SQL.

Here is the code,


Option Explicit

Dim sh As Worksheet
Dim cnn As adodb.Connection

Public Sub RefreshData()
    Set sh = Sheets("Orders")
    Dim rs As New adodb.Recordset
    Dim sql As String
    Set cnn = New adodb.Connection
   
    cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=ccdbsa1515!;Initial Catalog=CourierComplete;Data Source=Wassim-Laptop;"

    sql = "select OrderNO,OrderDate,CustomField1,CustomField2, WaybillNumber from ActiveOrders WHERE accountnumber in (3333)"
    'Delete old data
    sh.Range("A2:H65000").ClearContents
   
    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
End Sub

Public Sub UpdateData()
    Set sh = Sheets("Orders")

    Set cnn = New adodb.Connection
    cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=ccdbsa1515!;Initial Catalog=CourierComplete;Data Source=Wassim-Laptop;"

    Dim i As Integer
   
    'Find rows that have been modified
    For i = 2 To Range("A1").End(xlDown).Row
        If Cells(i, 8) = "Modified" Then
        UpdateRow (i)
        End If
    Next i
    cnn.Close
End Sub

Private Sub UpdateRow(rowIndex As Integer)
    Dim sql As String
    sql = "UPDATE Activeorders SET WaybillNumber = '" & sh.Cells(rowIndex, 4) & "' WHERE ActiveOrders.Orderno = " & sh.Cells(rowIndex, 1)
   
    Dim cmd As New adodb.Recordset
    cmd.Open sql, cnn, adOpenDynamic, adLockOptimistic
    cmd.Close
End Sub


thank you
0
 
RyanProject Engineer, ElectricalCommented:
Then your button is executing the RefreshData function.

The button to update the database should call UpdateData function.

UpdateRow function still needs updated to ADODB.Command as I pointed out prior. Right now it just changed the variable name from rs to cmd.
0
 
W.E.BAuthor Commented:
Hello,
I appreciate your time and patience.
I'm newbie here.

I attached the excel.
the buttons are linked to the proper macro.

still not working.
I'm not sure how to add (UpdateRow function still needs updated to ADODB.),.

Thanks again.
Excel2SQL.xls
0
 
RyanProject Engineer, ElectricalCommented:
replace UpdateRow with this.

Private Sub UpdateRow(rowIndex As Integer)
    Dim sql As String
    sql = "UPDATE Activeorders SET WaybillNumber = '" & sh.Cells(rowIndex, 4) & "' WHERE ActiveOrders.Orderno = " & sh.Cells(rowIndex, 1)
   
    Dim cmd As New adodb.Command
    cmd.ActiveConnection = cnn
    cmd.Execute
End Sub
0
 
W.E.BAuthor Commented:
I get Error
Run-Time error'-2147217908 (80040e0c)
Command text was not set for the command object.

cmd.Execute
0
 
RyanProject Engineer, ElectricalCommented:
I'm making careless errors in haste

cmd.ActiveConnection = cnn
cmd.CommandText = sql
cmd.Execute
0
 
W.E.BAuthor Commented:
Beautiful, thank you.
small question,,
is there any line you can add to tell the records updated?
0
 
RyanProject Engineer, ElectricalCommented:
to the end of UpdateData add the line

msgbox "Update Completed"
0
 
W.E.BAuthor Commented:
ok,  but the number of records? is that possible?
0
 
RyanProject Engineer, ElectricalCommented:
Public Sub UpdateData()
    Set sh = Sheets("Orders")
dim cnt as integer

    Set cnn = New adodb.Connection
    cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=ccdbsa1515!;Initial Catalog=CourierComplete;Data Source=Wassim-Laptop;"

    Dim i As Integer
    
    'Find rows that have been modified
    For i = 2 To Range("A1").End(xlDown).Row
        If Cells(i, 8) = "Modified" Then
        UpdateRow (i)
cnt=cnt+1
        End If
    Next i
    cnn.Close
msgbox cnt & " records updated"
End Sub

Open in new window

0
 
W.E.BAuthor Commented:
Perfect,
Thank you very very much for all your time and help.
 you have no idea how much time and effort this will save me..

Thanks a lot.
0
 
W.E.BAuthor Commented:
Perfect,
Thank you very very much for all your time and help.
 you have no idea how much time and effort this will save me..

Thanks a lot.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 17
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now