Excel: VBA Update Cells On Web Query Refresh

On a web query refresh, I would like to update cells B18 and C18 on Sheet2 (moving down a row on each new refresh) based on the values of $J$16 and $P$16 respectively of Sheet1.

Please advise?

Best wishes,

John
jfdinneenAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Here's a demo using a Querytable object declared WithEvents. Code is:
ThisWorkbook module:
Option Explicit

Private Sub Workbook_Open()
   Sheet7.HookQuery
End Sub

Open in new window


Worksheet code module:
Option Explicit

Private WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
   Dim lngRow            As Long
   Dim wksOut            As Worksheet
   Set wksOut = Sheets("Sheet2")
   With wksOut
      lngRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
      .Cells(lngRow, "B").Value = Me.Range("C1").Value
      .Cells(lngRow, "C").Value = Me.Range("C2").Value
   End With

End Sub

Public Sub HookQuery()
   Set qt = Me.QueryTables(1)
End Sub

Open in new window


Just alter the value cells from C1 and C2 to whatever cells you want.

Webquery.xlsm
0
 
Rory ArchibaldCommented:
Which version of Excel?
0
 
jfdinneenAuthor Commented:
Rorya,

Excel 2007

Best wishes,

John
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jfdinneenAuthor Commented:
Rorya,

Thanks for the high-quality solution.

One problem - it does not work if I have another spreadsheet in focus - which is critical as I need the updates to take place in the background.

Best wishes,

John
0
 
Rory ArchibaldCommented:
Sorry - lazy coding. Chang ethis line:

   Set wksOut = Sheets("Sheet2")

to this:


   Set wksOut = ThisWorkbook.Sheets("Sheet2")

adjusting the sheet name as required. Better yet, use the sheet's codename.
0
 
jfdinneenAuthor Commented:
Excellent resolution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.