Refreshing Web Query & Saving

[qcan]

Hi,

I am unfortunatly not very proficient in Excel. I have created a
simple web query that 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.

 

[EricG]

Why try to move the web query? Just move the data instead. The example
below uses the 'Worksheet_Change' event of the sheet with the web query to
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 the web
query. Select "View Code". Copy the code below and paste it into the Visual
Basic Editor window. The code will run every time your web query updates 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]

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 haveto
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 theVisual
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








- Show quoted text -

Thanks very much Eric. It works flawlessly !!!