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

Dear Harry:

The code itself doesn't include the link to the webpage. Instead it only
refreshes an existing web query.

To create a web query go to the Data menu, then under Import External Data.

There you can create a web query that can be updated. That way you can
insert a dynamic value (or table) into your spreadsheet.

Hope that helps, and if it does please rate my post.

G.Morales.

"Harry Sampson" wrote:

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

Mexage wrote...
Dear Harry:

The code itself doesn't include the link to the webpage. Instead it only
refreshes an existing web query.

To create a web query go to the Data menu, then under Import External
Data.

There you can create a web query that can be updated. That way you can
insert a dynamic value (or table) into your spreadsheet.

Hope that helps, and if it does please rate my post.

G.Morales.

Hi G. Morales,

Thank you for your assistance.

As far as rating your posts, I am posting this message to the Excel
Newsgroup. I suspect you are using some third-party forum, which is simply
picking up Newsgroup feeds. So I am not registered to your forum and thus
can't provide you with a rating.

HS

I am using the Office Online Discussion Groups Webpage.

It's in Microsoft.com

I'm glad that helped you!

"Harry Sampson" wrote:


Mexage wrote...
Dear Harry:

The code itself doesn't include the link to the webpage. Instead it only
refreshes an existing web query.

To create a web query go to the Data menu, then under Import External
Data.

There you can create a web query that can be updated. That way you can
insert a dynamic value (or table) into your spreadsheet.

Hope that helps, and if it does please rate my post.

G.Morales.

Hi G. Morales,

Thank you for your assistance.

As far as rating your posts, I am posting this message to the Excel
Newsgroup. I suspect you are using some third-party forum, which is simply
picking up Newsgroup feeds. So I am not registered to your forum and thus
can't provide you with a rating.

HS