help refreshing List programmatically

[dhx10000]

Hi,

I have a list in an excel worksheet that I need to refresh upon the file opening, but I need to do it through VB. The reason is because after the list is refreshed, I then need to copy some formulas in adjacent columns. I have tried doing this by "checking the appropriate values" but I am not getting accurate results.

I tried using the code below but my Refresh command is ignored. I know this because I added a new record in the source data and it did not appear in the excel sheet, upon opening the file:

Private Sub Workbook_Open()

ThisWorkbook.RefreshAll

'Range("K2") = "=HyperLinkText(B2)"
Range("K2") = "=IF(HyperLinkText(B2)=""NotAvailable.aspx?PageView=Shared"","""",HyperLinkText(B2))"
Range("K2").Select
Range("K2").Copy
Range("K2:K" & Application.WorksheetFunction.CountA(Range("A:A"))).Select
Range("K2:K" & Application.WorksheetFunction.CountA(Range("A:A"))).PasteSpecial

End Sub

 

[Reid]

I assume this list is a QueryTable? If so, this structure should work (not tested):

Dim wBook as Workbook, wSheet as Worksheet, qTable as QueryTable

Set wBook=ThisWorkbook
For Each wSheet in wBook.Worksheets
For Each qTable in wSheet
qTable.Refresh
Next qTable
Next wSheet

Hope this helps.