Can I get excel to refresh ~500 queries?
I have set up a workbook which contains ~500 individual queries (one for each sheet in the workbook where I am trying to collect daily information from the web.) I can refresh each query individually without issues, but that clearly take a lot of manual operations. When I attempt to refresh all, the workbook freezes for about an hour, then when I get control back I get a message that most of the queries timed out.
Is there a way to have excel refresh all of these queries?
I used the following code in VBA to create the queries.
Sub StatsFromWeb()
Dim i As Integer
Dim n As Integer
Dim Name As String
Dim ESPNID As String
n = Sheets("1-Player Roster").Range("A1", Sheets("1-Player Roster").Range("A1").End(xlDown)).Rows.Count
For i = 1 To n
DoEvents
Name = Sheets("1-Player Roster").Cells(i, 1).Value
ESPNID = Sheets("1-Player Roster").Cells(i, 2).Value
Sheets(Name).Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.espn.com/nba/player/gamelog/_/id/" & ESPNID & "/", Destination:=Range("$AA$1"))
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Next
End Sub
excel refresh
asked Nov 23 '18 at 22:40
Timothy DuttonTimothy Dutton
61
add a comment |
I have set up a workbook which contains ~500 individual queries (one for each sheet in the workbook where I am trying to collect daily information from the web.) I can refresh each query individually without issues, but that clearly take a lot of manual operations. When I attempt to refresh all, the workbook freezes for about an hour, then when I get control back I get a message that most of the queries timed out.
Is there a way to have excel refresh all of these queries?
I used the following code in VBA to create the queries.
Sub StatsFromWeb()
Dim i As Integer
Dim n As Integer
Dim Name As String
Dim ESPNID As String
n = Sheets("1-Player Roster").Range("A1", Sheets("1-Player Roster").Range("A1").End(xlDown)).Rows.Count
For i = 1 To n
DoEvents
Name = Sheets("1-Player Roster").Cells(i, 1).Value
ESPNID = Sheets("1-Player Roster").Cells(i, 2).Value
Sheets(Name).Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.espn.com/nba/player/gamelog/_/id/" & ESPNID & "/", Destination:=Range("$AA$1"))
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Next
End Sub
excel refresh
asked Nov 23 '18 at 22:40
Timothy DuttonTimothy Dutton
61
Could you provide a sample of your excel?
– yoonghm
Nov 24 '18 at 1:26
Why do you have 500 different queries? Wouldn't it be possible to write just ONE query for all the data and use pivot tables with filters for the 500 different report scenarios?
– teylyn
Nov 24 '18 at 5:27
Teylyn, that would be great. Is there a way to have one query pull data from multiple web addresses? I'm not sure how I can consolidate.
– Timothy Dutton
Nov 25 '18 at 13:42
add a comment |
I have set up a workbook which contains ~500 individual queries (one for each sheet in the workbook where I am trying to collect daily information from the web.) I can refresh each query individually without issues, but that clearly take a lot of manual operations. When I attempt to refresh all, the workbook freezes for about an hour, then when I get control back I get a message that most of the queries timed out.
Is there a way to have excel refresh all of these queries?
I used the following code in VBA to create the queries.
Sub StatsFromWeb()
Dim i As Integer
Dim n As Integer
Dim Name As String
Dim ESPNID As String
n = Sheets("1-Player Roster").Range("A1", Sheets("1-Player Roster").Range("A1").End(xlDown)).Rows.Count
For i = 1 To n
DoEvents
Name = Sheets("1-Player Roster").Cells(i, 1).Value
ESPNID = Sheets("1-Player Roster").Cells(i, 2).Value
Sheets(Name).Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.espn.com/nba/player/gamelog/_/id/" & ESPNID & "/", Destination:=Range("$AA$1"))
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Next
End Sub
excel refresh
asked Nov 23 '18 at 22:40
Timothy DuttonTimothy Dutton
61
I have set up a workbook which contains ~500 individual queries (one for each sheet in the workbook where I am trying to collect daily information from the web.) I can refresh each query individually without issues, but that clearly take a lot of manual operations. When I attempt to refresh all, the workbook freezes for about an hour, then when I get control back I get a message that most of the queries timed out.
Is there a way to have excel refresh all of these queries?
I used the following code in VBA to create the queries.
Sub StatsFromWeb()
Dim i As Integer
Dim n As Integer
Dim Name As String
Dim ESPNID As String
n = Sheets("1-Player Roster").Range("A1", Sheets("1-Player Roster").Range("A1").End(xlDown)).Rows.Count
For i = 1 To n
DoEvents
Name = Sheets("1-Player Roster").Cells(i, 1).Value
ESPNID = Sheets("1-Player Roster").Cells(i, 2).Value
Sheets(Name).Activate
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.espn.com/nba/player/gamelog/_/id/" & ESPNID & "/", Destination:=Range("$AA$1"))
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Next
End Sub
excel refresh
excel refresh
asked Nov 23 '18 at 22:40
Timothy DuttonTimothy Dutton
61
asked Nov 23 '18 at 22:40
Timothy DuttonTimothy Dutton
61
asked Nov 23 '18 at 22:40
Timothy DuttonTimothy Dutton
61
asked Nov 23 '18 at 22:40
Timothy DuttonTimothy Dutton
61
asked Nov 23 '18 at 22:40
Timothy DuttonTimothy Dutton
61
61
Could you provide a sample of your excel?
– yoonghm
Nov 24 '18 at 1:26
Why do you have 500 different queries? Wouldn't it be possible to write just ONE query for all the data and use pivot tables with filters for the 500 different report scenarios?
– teylyn
Nov 24 '18 at 5:27
Teylyn, that would be great. Is there a way to have one query pull data from multiple web addresses? I'm not sure how I can consolidate.
– Timothy Dutton
Nov 25 '18 at 13:42
add a comment |
Could you provide a sample of your excel?
– yoonghm
Nov 24 '18 at 1:26
Why do you have 500 different queries? Wouldn't it be possible to write just ONE query for all the data and use pivot tables with filters for the 500 different report scenarios?
– teylyn
Nov 24 '18 at 5:27
Teylyn, that would be great. Is there a way to have one query pull data from multiple web addresses? I'm not sure how I can consolidate.
– Timothy Dutton
Nov 25 '18 at 13:42
Could you provide a sample of your excel?
– yoonghm
Nov 24 '18 at 1:26
Could you provide a sample of your excel?
– yoonghm
Nov 24 '18 at 1:26
Why do you have 500 different queries? Wouldn't it be possible to write just ONE query for all the data and use pivot tables with filters for the 500 different report scenarios?
– teylyn
Nov 24 '18 at 5:27
Why do you have 500 different queries? Wouldn't it be possible to write just ONE query for all the data and use pivot tables with filters for the 500 different report scenarios?
– teylyn
Nov 24 '18 at 5:27
Teylyn, that would be great. Is there a way to have one query pull data from multiple web addresses? I'm not sure how I can consolidate.
– Timothy Dutton
Nov 25 '18 at 13:42
Teylyn, that would be great. Is there a way to have one query pull data from multiple web addresses? I'm not sure how I can consolidate.
– Timothy Dutton
Nov 25 '18 at 13:42
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53453595%2fcan-i-get-excel-to-refresh-500-queries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53453595%2fcan-i-get-excel-to-refresh-500-queries%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Could you provide a sample of your excel?
– yoonghm
Nov 24 '18 at 1:26
Why do you have 500 different queries? Wouldn't it be possible to write just ONE query for all the data and use pivot tables with filters for the 500 different report scenarios?
– teylyn
Nov 24 '18 at 5:27
Teylyn, that would be great. Is there a way to have one query pull data from multiple web addresses? I'm not sure how I can consolidate.
– Timothy Dutton
Nov 25 '18 at 13:42