H
Harry Sampson
Hi,
I inherited a spreadsheet with a VBA module. This module updates financial
data from various websites. Below is the code.
I don't understand how "Selection.QueryTable.Refresh BackgroundQuery:=False"
updates the spreadsheet. Or put differently, I have no knowledge of
"QueryTable". Can someone point me to how the code below updates the
spreadsheet.
In looking at the spreadsheet, I didn't see any links the websites of
interest. So I am curious as to how this code works, and how it knows what
to fetch and where to fetch the data from the external websites.
Thank you.
HS
Sub Update_IntRates()
Set CurrentSheet = ActiveCell.Worksheet
Dim LRVisible As Integer
'
If Sheets("Mystery").Visible = False Then
LRVisible = 1
Sheets("Mystery").Visible = True
Else
LRVisible = 0
End If
'Prepare to Refresh Data
'
Sheets("Mystery").Select
'
Range("A57").Select
Application.StatusBar = "Updating Rates (Site 1 of 3)...Please Wait"
Selection.QueryTable.Refresh BackgroundQuery:=False
'Update 1 and 10 year FactorX Rates
'
'Copy timestamp
Application.StatusBar = "Applying Changes to Model...Please Wait"
Range("R2").Copy
Range("ratedate2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Prepare to update history
Range("U3:Y3").Select
Selection.Insert Shift:=xlDown
'Insert Date
Range("ratedate2").Copy
Range("U3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Update 3 month FactorX history
Range("FactorX_3mo").Copy
Range("V3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Update 1 year FactorX history
Range("FactorX_1yr").Copy
Range("W3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Update 10 year FactorX history
Range("FactorX_10yr").Copy
Range("X3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
CurrentSheet.Activate
Application.StatusBar = False
End Sub
I inherited a spreadsheet with a VBA module. This module updates financial
data from various websites. Below is the code.
I don't understand how "Selection.QueryTable.Refresh BackgroundQuery:=False"
updates the spreadsheet. Or put differently, I have no knowledge of
"QueryTable". Can someone point me to how the code below updates the
spreadsheet.
In looking at the spreadsheet, I didn't see any links the websites of
interest. So I am curious as to how this code works, and how it knows what
to fetch and where to fetch the data from the external websites.
Thank you.
HS
Sub Update_IntRates()
Set CurrentSheet = ActiveCell.Worksheet
Dim LRVisible As Integer
'
If Sheets("Mystery").Visible = False Then
LRVisible = 1
Sheets("Mystery").Visible = True
Else
LRVisible = 0
End If
'Prepare to Refresh Data
'
Sheets("Mystery").Select
'
Range("A57").Select
Application.StatusBar = "Updating Rates (Site 1 of 3)...Please Wait"
Selection.QueryTable.Refresh BackgroundQuery:=False
'Update 1 and 10 year FactorX Rates
'
'Copy timestamp
Application.StatusBar = "Applying Changes to Model...Please Wait"
Range("R2").Copy
Range("ratedate2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Prepare to update history
Range("U3:Y3").Select
Selection.Insert Shift:=xlDown
'Insert Date
Range("ratedate2").Copy
Range("U3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Update 3 month FactorX history
Range("FactorX_3mo").Copy
Range("V3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Update 1 year FactorX history
Range("FactorX_1yr").Copy
Range("W3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Update 10 year FactorX history
Range("FactorX_10yr").Copy
Range("X3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
CurrentSheet.Activate
Application.StatusBar = False
End Sub