Solved

Excel: VBA Update Cells On Web Query Refresh

Posted on 2011-09-15
6
183 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:jfdinneen
  • 3
  • 3
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36548128
Which version of Excel?
0
 

Author Comment

by:jfdinneen
ID: 36548633
Rorya,

Excel 2007

Best wishes,

John
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 36559502
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:jfdinneen
ID: 36560391
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36560449
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
 

Author Closing Comment

by:jfdinneen
ID: 36560610
Excellent resolution.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now