VBA to Update Excel Table: Please advice...

[pedie]

Hi, currently i'm using normal code to update my table.
Is there another way to update table...maybe something like updating access table?

table!table1.field1.value = 12

Something like this?


Please advice...
Thanks in advance

[/FONT]
[FONT=Courier New]Sub Macro3()
Sheets("Feb").Range("Table1").Activate
Dim x As Long
x = Sheets("Feb").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Row
With Sheets("Feb")
 .Range("D" & x).Value = 12
 .Range("E" & x).Value = 12
 .Range("F" & x).Value = 12
 .Range("G" & x).Value = 12
 .Range("H" & x).Value = 12
 .Range("I" & x).Value = 12
 .Range("J" & x).Value = 12
End With
End Sub

 

[JackDanIce]

Not sure about your query, but you could change your code to:

Sub Macro3()
Sheets("Feb").Range("Table1").Activate
Dim x As Long

With Sheets("Feb")
 x = .Range("D" & Rows.Count).End(xlUp).Offset(1,0).Row
 .Range("D" & x & ":J" & x).Value = 12
End With

End Sub

 

[Ruddles]

I'm rushing out - I'm late already! - but yes, you can use SQL to carry out updates to a worksheet.

Here's a bit of code I've used to update a field called Class in a table called tblPupil in the current workbook:-

Option Explicit
 
Public Sub UpdateQuery()
 
  Dim rsConn As ADODB.Connection
  Dim strConnect As String
  Dim rsData As ADODB.Recordset
  Dim strSQL As String
   
  Set rsConn = New ADODB.Connection
  strConnect = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
              "Data Source=" & ThisWorkbook.FullName & ";" & _
              "Extended Properties=Excel 12.0;"
  rsConn.Open strConnect
  
  If rsConn.State <> 1 Then
    MsgBox "Connection failed" & Space(15), vbOKOnly + vbExclamation
    Exit Sub
  End If
 
  strSQL = "UPDATE [COLOR=red][B]tblPupil[/B][/COLOR] SET [COLOR=red][B]Class[/B][/COLOR]='RTW' WHERE [COLOR=red][B]Class[/B][/COLOR]='RW';"
  
  Set rsData = New ADODB.Recordset
  rsData.Open strSQL, rsConn, 3, 1, 1
  
  Set rsData = Nothing
  Set rsConn = Nothing
 
End Sub

Is this any help?

I'll be back in a couple of hours and I'll check back in to the forum then.

 

[pedie]

Thanks Jack...but the thing is that i'm looking something like using query sort of to update excel table and not access....

Ruddles, thanks for helping me out!!! not sure how to make use of this code...Maybe be you can help me out by a little bit of explaination...
and pls dont rush... help me out whenever you're free...

Thanks again for helping!

P.S:
I have book1 and book2 where books 2, sheet2 has table called table1.
I want to update table 1 from book1 sheet1 A1:J1 value to table1.


Thanks again.

 

[Ruddles]

If you run this from book1, this will update field1 in table table1 in book2 with the value 12:-

Option Explicit
 
Public Sub UpdateQuery()
 
  Dim rsConn As ADODB.Connection
  Dim strConnect As String
  Dim rsData As ADODB.Recordset
  Dim strSQL As String
   
  Set rsConn = New ADODB.Connection
  strConnect = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
              "Data Source=[COLOR=red][B]c:\folder\book2.xls[/B][/COLOR];" & _
              "Extended Properties=Excel 12.0;"
  rsConn.Open strConnect
  
  If rsConn.State <> 1 Then
    MsgBox "Connection failed" & Space(15), vbOKOnly + vbExclamation
    Exit Sub
  End If
 
  strSQL = "UPDATE [COLOR=red][B]table1[/B][/COLOR] SET [COLOR=red][B]field1[/B][/COLOR]=12;" 
  
  Set rsData = New ADODB.Recordset
  rsData.Open strSQL, rsConn, 3, 1, 1
  
  Set rsData = Nothing
  Set rsConn = Nothing
 
End Sub

That's what you asked about in your first post. However you've now said you want to update each record in table1 with a different value, so the SQL would be a lot different as it would be treating each record in the table differently. I couldn't write that off the top of my head.

If you just want to copy a range from one workbook to another, you're probably best off using a Copy ws1.{from_range} Destination:=ws2.{to_range} sort of command.

 

[pedie]

Ruddles, I really appriciate your help!
That was a new learning...the second you provided i change the name of the table to suit and the file path but it errors...
table1 does not exist or file path is incorrect...

it erros in line

rsData.Open strSQL, rsConn, 3, 1, 1

Thanks again

 

[Ruddles]

The code needs to be in book1, you need to change strConnect to point to book2 and table1 needs to be defined as a named range in the scope of the workbook in book2.

 

[pedie]

Ruddles, okay thanks again...!!!