Issue with PasteSpecial after copying from refreshed SQL data source

[tianimo]

Hi,

I've got an issue with copying and pasting values from an SQL data source. I'm using Excel 2007.

I have set up an SQL data connection which calls a Stored Procedure in a database, retrieves a couple of values, and displays them in a sheet in the workbook.

I have a sub which can pass a user-defined parameter to the connection and update the call to the SP, so the right data is retrieved based on the user-defined input. This works fine and has been tested by repeatedly feeding it different inputs one after the other.

I am now incorporating this in to a routine where I refresh the SP connection based on the user input, retrieve the values, and then copy and paste special the values in to a different sheet where I will manipulate them.

This process works fine the first time; however, if I change the variable and run the process again, even though I can see (by stepping through the code line by line) that the SP is refreshing and retrieving the new values, when I perform the (what follows is just illustrative)

Range("A1:B1").Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


it is pasting the previous details that were retrieved, and not the new ones!

I thought it might be that I should clear the clipboard, but having added " Application.CutCopyMode = False " in to the code doesn't help. Sometimes I find if I save the workbook and then rerun the procedure it's fine, but not always - anyway, forcing a save just to get round the issue seems like a terible hack rather than understanding the problem and resolving it!

Any help would be really appreciated. Thanks.

 

[tianimo]

I've just done more testing. If I put a break in the code at the point when I am copying and pasting the values and step through the procedure, it works every time. If I remove the breaks and let it run from start to finish, it shows the odd behaviour described above.