Refreshing Web Query & Saving
On Jul 8, 6:03*pm, EricG wrote:
Why try to move thewebquery? *Just move the data instead. *The example
below uses the 'Worksheet_Change' event of the sheet with thewebqueryto
trigger a copy/paste operation that copies the updated data to the clipboard
and then pastes it at the end of the data on another sheet. *The routine also
adds a time stamp in the next available column. *You will probably have to
modify it a little to suit your needs. *I assumed that the sheet you save the
data on will be named "Saved Data".
Instructions: *Right-click on the worksheet tab of the sheet with thewebquery. *Select "View Code". *Copy the code below and paste it into the Visual
Basic Editor window. *The code will run every time yourwebqueryupdates the
data.
HTH,
Eric
Private Sub Worksheet_Change(ByVal Target As Range)
* * Dim dataRange As Range
* * Dim wsCurrent As String, wsName As String
* * wsCurrent = ActiveSheet.Name
* * Me.Select ' Select the sheet with the change event
* * wsName = Me.Name ' Save the sheet name
* * Set dataRange = ActiveWorkbook.Worksheets(wsName).Range("A1:F15")
'
' If the data in our target block changed, do the
' stuff below.
'
* * If (Not Intersect(Target, dataRange) Is Nothing) Then
* * * * Application.ScreenUpdating = False
* * * * * * Application.EnableEvents = False
* * * * * * * * dataRange.Select
* * * * * * * * Selection.Copy
* * * * * * * * ActiveWorkbook.Worksheets("Saved Data").Select
* * * * * * * * ActiveSheet.Cells(ActiveSheet.Rows.Count,
"A").End(xlUp).Offset(1, 0).Select
* * * * * * * * Selection = dataRange
* * * * * * * * ActiveSheet.Paste
* * * * * * * * ActiveCell.Offset(0, 6).Resize(15, 1).Select
* * * * * * * * ActiveCell.Value = Now() *' Apply a time stamp to the data
* * * * * * * * Selection.FillDown
* * * * * * * * ActiveWorkbook.Worksheets(wsCurrent).Select
* * * * * * Application.EnableEvents = True
* * * * Application.ScreenUpdating = True
* * End If
'
End Sub
"qcan" wrote:
Hi,
I am unfortunatly not very proficient in Excel. I have created a
simplewebquerythat refreshes every minute. However, what I would
like it to do is to save after each refresh is to "time stamp" and go
to the next available line where the next refrehable data would go
until I finally press the break button.
Example.
- 1st data returned starts at cell A1 to F15.
- Then a time stamp in cells G1 to G15.
- Save
- On the next refresh, the next available cell ( A16 ) is used etc
etc.
Any help would be greatly appreciated.
Thanks.- Hide quoted text -
- Show quoted text -
Thanks very much Eric. It works flawlessly !!!
|