Excel VBA code to refresh connection to SQL Server stored procedure

[mhghg]

Hi All,
I am using excel vba code to refresh connection to SQL server stored procedure.
The problem is I dont want it to refresh all my data, I just want it to append the extra bit of data to my excel sheet from last time it updated. Is there any way VBA code can help ?
Macy

 

[Debaser]

You can't refresh part of a connection. Unless there is a way (i.e. a criterion you can use in a WHERE clause) to filter the data from the SQL Server so that you only get the updates, you're probably out of luck.

Can I ask why it matters?

 

[mhghg]

Hi,
I actually used the Where condition in sql script. The reason is every it refreshes it will overwrite the old data I have working on it.

 

[mhghg]

Hello Guys,
As I cannot append the data to my current working sheet so I try to retrieve a section of the data and copy from sheet1 to sheet2 (working sheet) but I got error message [Application Error 1004] when I try to select Range "A2".select in sheet2. Pls explain why cannot?

Private Sub CommandButton1_Click()
Dim i As Integer
Dim tblCurrent As Range
Dim lRow, lasrRow As Long
Dim new_Date As Date
Dim old_Date As Date
Dim FoundCell As Range
Dim wb As Workbook

Set wb = ActiveWorkbook
Sheet1.Activate

'Show InputBox and Collect Data in a String Variable
new_Date = InputBox("Please date with this format [dd-mm-yyyy] to start with:", "Collect user Input")
'End Macro if Cancel Button is Clicked or no Text is Entered
If IsEmpty(new_Date) = True Then Exit Sub
Range("A6").Select
    With ActiveWorkbook.Connections("test").OLEDBConnection
    .CommandText = "usp_GetNewData '" & new_Date & "'"
    ActiveWorkbook.Connections("test".Refresh
    End With
With Sheet1
Set FoundCell = .Cells.Find("Name").Offset(1, 0)
'Define the first row of the data
lRow = .Cells(.Rows.Count, 1).End(xlUp).Row - FoundCell.Row + 1
Debug.Print TypeName(lRow), lRow
'Set up the dynamic Table of Data
Set tblCurrent = FoundCell.Resize(lRow, 7)
'Adding 5 more rows as data starting at row 6
tblCurrent.Copy
'old_Date = Range("D" & lRow + 5).Value
End With
Sheets("Sheet2").Activate

With Sheet2
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Range("A1").Offset(lastRow, 0).Select
ActiveSheet.Paste
End With

End Sub

 

[Debaser]

You missed a '.' out:

.Range("A1").Offset(lastRow, 0).Select

 

[mhghg]

Right I got it. Thanks very much Can you explain to me why in Sheet1 I use
Range("A6").Select I dont need the dot but sheet2 I do the same but it throws the error. And why we don't use .Activesheet.Paste?
And the other thing is when i try to re run the script with different date, it pastes the data in the memory not the new updated data. Any idea guys?

 

[mhghg]

Hi guys,
I pulled my hair for the whole trying different to paste the updated value, but for some reason it continues to paste the previous queried data. If I use F8 to step over then it works ok, but when i try to command button click the issue happenes. I am not sure where to find the old data as the office clipboard is cleared with application.cutcopymode = false.
Give up for today!

 

[Debaser]

Make sure the connection properties do not allw it to refresh in the background. That way it will have to finish refreshing before the code continues.

 

[Stevie]

As debaser said, try disabling the background refresh.
You can do this for a querytable with the below:

Sheets("sheet_name_here").Range("Cell_in _table_range").ListObject.QueryTable.Refresh BackgroundQuery:=False

 

[mhghg]

Hello Debaser and Stevie,
I got it worked now after I tried Stevie ways a few times, but it always throws up the error,then do more research on the net and finally I understood what Debaser means is when setting up the Data connection using external link, on connection property screen untick the option "Enable background Refresh".
I write here so any one having the same issue can check it again.
Thank you guys.