Individual files making program erratic performance
0
I am making individual daily price data files for Tickers from Daily Price Data file. Program code appended below is not working properly. Sometimes it runs and makes 60 files and then fails to save a file and subsequently it fails to paste the desired range data though it creates the files. Other times it may fail to save even after 5th file or may be after 30th file. I even introduced pause time .
Application.Wait (Now + #12:00:03 AM#)
But it does not solve the problem. Program fails on the following code line arbitrarily.
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell & "-v1.xls", FileFormat:=56
Program code is appended below.
Sub number()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet
Dim cell, rng As Range
Dim stRw As Long
Set rng = Range("A2:A72")
stRw = 2
For Each cell In rng
If cell.Value <> cell.Offset(1, 0).Value Then
Set wbI = ActiveWorkbook
Set wsI = wbI.Worksheets("Sheet1")
Set wbO = Workbooks.Add
With wbO
Set wsO = wbO.Sheets("Sheet1")
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell & "-v1.xls", FileFormat:=56
wsI.Range("A1:H1").Copy
wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wsI.Rows(stRw & ":" & cell.Row).Copy
wsO.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Close SaveChanges:=True
stRw = cell.Row + 1
End With
Set wbI = Nothing
Set wsI = Nothing
Set wbO = Nothing
Set wsO = Nothing
End If
Application.Wait (Now + #12:00:03 AM#)
Next cell
End Sub
Request help in resolving this issue.
excel vba excel-vba excel-2016
asked Nov 28 '18 at 14:25
skkakkarskkakkar
2,2312924
|
show 2 more comments
0
I am making individual daily price data files for Tickers from Daily Price Data file. Program code appended below is not working properly. Sometimes it runs and makes 60 files and then fails to save a file and subsequently it fails to paste the desired range data though it creates the files. Other times it may fail to save even after 5th file or may be after 30th file. I even introduced pause time .
Application.Wait (Now + #12:00:03 AM#)
But it does not solve the problem. Program fails on the following code line arbitrarily.
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell & "-v1.xls", FileFormat:=56
Program code is appended below.
Sub number()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet
Dim cell, rng As Range
Dim stRw As Long
Set rng = Range("A2:A72")
stRw = 2
For Each cell In rng
If cell.Value <> cell.Offset(1, 0).Value Then
Set wbI = ActiveWorkbook
Set wsI = wbI.Worksheets("Sheet1")
Set wbO = Workbooks.Add
With wbO
Set wsO = wbO.Sheets("Sheet1")
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell & "-v1.xls", FileFormat:=56
wsI.Range("A1:H1").Copy
wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wsI.Rows(stRw & ":" & cell.Row).Copy
wsO.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Close SaveChanges:=True
stRw = cell.Row + 1
End With
Set wbI = Nothing
Set wsI = Nothing
Set wbO = Nothing
Set wsO = Nothing
End If
Application.Wait (Now + #12:00:03 AM#)
Next cell
End Sub
Request help in resolving this issue.
excel vba excel-vba excel-2016
asked Nov 28 '18 at 14:25
skkakkarskkakkar
2,2312924
yourcellvariable is declared as a variant. Firstly, are you sure it has a value set? and Seconly try using.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56
– Tom
Nov 28 '18 at 14:30
What error do you get when it "fails on the following code line arbitrarily"?
– Comintern
Nov 28 '18 at 14:42
1
wsI.Range("A1:H1").Copyis a huge waste of resources. Instead of copying the entire column, only copy the range with data in it. This may be causing an overload especially because there's a long loop. You can also make it more efficient by removing copy/paste technique and settingrange.value = range.value, directly.
– Scott Holtzman
Nov 28 '18 at 14:53
@Tom your suggestion to add Value2 after Cell solves the problem. Please make it as an answer so that I may tick it. Thanks a lot.
– skkakkar
Nov 28 '18 at 14:54
@skkakkar Glad it worked - added
– Tom
Nov 28 '18 at 14:57
|
show 2 more comments
0
0
0
I am making individual daily price data files for Tickers from Daily Price Data file. Program code appended below is not working properly. Sometimes it runs and makes 60 files and then fails to save a file and subsequently it fails to paste the desired range data though it creates the files. Other times it may fail to save even after 5th file or may be after 30th file. I even introduced pause time .
Application.Wait (Now + #12:00:03 AM#)
But it does not solve the problem. Program fails on the following code line arbitrarily.
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell & "-v1.xls", FileFormat:=56
Program code is appended below.
Sub number()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet
Dim cell, rng As Range
Dim stRw As Long
Set rng = Range("A2:A72")
stRw = 2
For Each cell In rng
If cell.Value <> cell.Offset(1, 0).Value Then
Set wbI = ActiveWorkbook
Set wsI = wbI.Worksheets("Sheet1")
Set wbO = Workbooks.Add
With wbO
Set wsO = wbO.Sheets("Sheet1")
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell & "-v1.xls", FileFormat:=56
wsI.Range("A1:H1").Copy
wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wsI.Rows(stRw & ":" & cell.Row).Copy
wsO.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Close SaveChanges:=True
stRw = cell.Row + 1
End With
Set wbI = Nothing
Set wsI = Nothing
Set wbO = Nothing
Set wsO = Nothing
End If
Application.Wait (Now + #12:00:03 AM#)
Next cell
End Sub
Request help in resolving this issue.
excel vba excel-vba excel-2016
asked Nov 28 '18 at 14:25
skkakkarskkakkar
2,2312924
I am making individual daily price data files for Tickers from Daily Price Data file. Program code appended below is not working properly. Sometimes it runs and makes 60 files and then fails to save a file and subsequently it fails to paste the desired range data though it creates the files. Other times it may fail to save even after 5th file or may be after 30th file. I even introduced pause time .
Application.Wait (Now + #12:00:03 AM#)
But it does not solve the problem. Program fails on the following code line arbitrarily.
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell & "-v1.xls", FileFormat:=56
Program code is appended below.
Sub number()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet
Dim cell, rng As Range
Dim stRw As Long
Set rng = Range("A2:A72")
stRw = 2
For Each cell In rng
If cell.Value <> cell.Offset(1, 0).Value Then
Set wbI = ActiveWorkbook
Set wsI = wbI.Worksheets("Sheet1")
Set wbO = Workbooks.Add
With wbO
Set wsO = wbO.Sheets("Sheet1")
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell & "-v1.xls", FileFormat:=56
wsI.Range("A1:H1").Copy
wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wsI.Rows(stRw & ":" & cell.Row).Copy
wsO.Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Close SaveChanges:=True
stRw = cell.Row + 1
End With
Set wbI = Nothing
Set wsI = Nothing
Set wbO = Nothing
Set wsO = Nothing
End If
Application.Wait (Now + #12:00:03 AM#)
Next cell
End Sub
Request help in resolving this issue.
excel vba excel-vba excel-2016
excel vba excel-vba excel-2016
asked Nov 28 '18 at 14:25
skkakkarskkakkar
2,2312924
asked Nov 28 '18 at 14:25
skkakkarskkakkar
2,2312924
asked Nov 28 '18 at 14:25
skkakkarskkakkar
2,2312924
asked Nov 28 '18 at 14:25
skkakkarskkakkar
2,2312924
asked Nov 28 '18 at 14:25
skkakkarskkakkar
2,2312924
2,2312924
yourcellvariable is declared as a variant. Firstly, are you sure it has a value set? and Seconly try using.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56
– Tom
Nov 28 '18 at 14:30
What error do you get when it "fails on the following code line arbitrarily"?
– Comintern
Nov 28 '18 at 14:42
1
wsI.Range("A1:H1").Copyis a huge waste of resources. Instead of copying the entire column, only copy the range with data in it. This may be causing an overload especially because there's a long loop. You can also make it more efficient by removing copy/paste technique and settingrange.value = range.value, directly.
– Scott Holtzman
Nov 28 '18 at 14:53
@Tom your suggestion to add Value2 after Cell solves the problem. Please make it as an answer so that I may tick it. Thanks a lot.
– skkakkar
Nov 28 '18 at 14:54
@skkakkar Glad it worked - added
– Tom
Nov 28 '18 at 14:57
|
show 2 more comments
yourcellvariable is declared as a variant. Firstly, are you sure it has a value set? and Seconly try using.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56
– Tom
Nov 28 '18 at 14:30
What error do you get when it "fails on the following code line arbitrarily"?
– Comintern
Nov 28 '18 at 14:42
1
wsI.Range("A1:H1").Copyis a huge waste of resources. Instead of copying the entire column, only copy the range with data in it. This may be causing an overload especially because there's a long loop. You can also make it more efficient by removing copy/paste technique and settingrange.value = range.value, directly.
– Scott Holtzman
Nov 28 '18 at 14:53
@Tom your suggestion to add Value2 after Cell solves the problem. Please make it as an answer so that I may tick it. Thanks a lot.
– skkakkar
Nov 28 '18 at 14:54
@skkakkar Glad it worked - added
– Tom
Nov 28 '18 at 14:57
your
cell variable is declared as a variant. Firstly, are you sure it has a value set? and Seconly try using .SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56– Tom
Nov 28 '18 at 14:30
your
cell variable is declared as a variant. Firstly, are you sure it has a value set? and Seconly try using .SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56– Tom
Nov 28 '18 at 14:30
What error do you get when it "fails on the following code line arbitrarily"?
– Comintern
Nov 28 '18 at 14:42
What error do you get when it "fails on the following code line arbitrarily"?
– Comintern
Nov 28 '18 at 14:42
1
1
wsI.Range("A1:H1").Copy is a huge waste of resources. Instead of copying the entire column, only copy the range with data in it. This may be causing an overload especially because there's a long loop. You can also make it more efficient by removing copy/paste technique and setting range.value = range.value, directly.– Scott Holtzman
Nov 28 '18 at 14:53
wsI.Range("A1:H1").Copy is a huge waste of resources. Instead of copying the entire column, only copy the range with data in it. This may be causing an overload especially because there's a long loop. You can also make it more efficient by removing copy/paste technique and setting range.value = range.value, directly.– Scott Holtzman
Nov 28 '18 at 14:53
@Tom your suggestion to add Value2 after Cell solves the problem. Please make it as an answer so that I may tick it. Thanks a lot.
– skkakkar
Nov 28 '18 at 14:54
@Tom your suggestion to add Value2 after Cell solves the problem. Please make it as an answer so that I may tick it. Thanks a lot.
– skkakkar
Nov 28 '18 at 14:54
@skkakkar Glad it worked - added
– Tom
Nov 28 '18 at 14:57
@skkakkar Glad it worked - added
– Tom
Nov 28 '18 at 14:57
|
show 2 more comments
1 Answer
1
active
oldest
votes
0
You're using a range variant to concatenate your String FilePath
Use cell.Value2 to concatenate the cell value with your String FilePath.
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56
answered Nov 28 '18 at 14:57
TomTom
6,56511843
1
It's also worth pointing out that writingDim cellasDim cell as Rangewould also solve the issue, because.Valueis the default property of a range object. It's also a wiser solution because the code does not use a variant data type in an unnecessary way.
– Scott Holtzman
Nov 28 '18 at 15:10
add a comment |
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
});
}
});
draft saved
draft discarded
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%2f53521655%2findividual-files-making-program-erratic-performance%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You're using a range variant to concatenate your String FilePath
Use cell.Value2 to concatenate the cell value with your String FilePath.
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56
answered Nov 28 '18 at 14:57
TomTom
6,56511843
1
It's also worth pointing out that writingDim cellasDim cell as Rangewould also solve the issue, because.Valueis the default property of a range object. It's also a wiser solution because the code does not use a variant data type in an unnecessary way.
– Scott Holtzman
Nov 28 '18 at 15:10
add a comment |
0
You're using a range variant to concatenate your String FilePath
Use cell.Value2 to concatenate the cell value with your String FilePath.
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56
answered Nov 28 '18 at 14:57
TomTom
6,56511843
1
It's also worth pointing out that writingDim cellasDim cell as Rangewould also solve the issue, because.Valueis the default property of a range object. It's also a wiser solution because the code does not use a variant data type in an unnecessary way.
– Scott Holtzman
Nov 28 '18 at 15:10
add a comment |
0
0
0
You're using a range variant to concatenate your String FilePath
Use cell.Value2 to concatenate the cell value with your String FilePath.
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56
answered Nov 28 '18 at 14:57
TomTom
6,56511843
You're using a range variant to concatenate your String FilePath
Use cell.Value2 to concatenate the cell value with your String FilePath.
.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56
answered Nov 28 '18 at 14:57
TomTom
6,56511843
answered Nov 28 '18 at 14:57
TomTom
6,56511843
answered Nov 28 '18 at 14:57
TomTom
6,56511843
answered Nov 28 '18 at 14:57
TomTom
6,56511843
6,56511843
1
It's also worth pointing out that writingDim cellasDim cell as Rangewould also solve the issue, because.Valueis the default property of a range object. It's also a wiser solution because the code does not use a variant data type in an unnecessary way.
– Scott Holtzman
Nov 28 '18 at 15:10
add a comment |
1
It's also worth pointing out that writingDim cellasDim cell as Rangewould also solve the issue, because.Valueis the default property of a range object. It's also a wiser solution because the code does not use a variant data type in an unnecessary way.
– Scott Holtzman
Nov 28 '18 at 15:10
1
1
It's also worth pointing out that writing
Dim cell as Dim cell as Range would also solve the issue, because .Value is the default property of a range object. It's also a wiser solution because the code does not use a variant data type in an unnecessary way.– Scott Holtzman
Nov 28 '18 at 15:10
It's also worth pointing out that writing
Dim cell as Dim cell as Range would also solve the issue, because .Value is the default property of a range object. It's also a wiser solution because the code does not use a variant data type in an unnecessary way.– Scott Holtzman
Nov 28 '18 at 15:10
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53521655%2findividual-files-making-program-erratic-performance%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
your
cellvariable is declared as a variant. Firstly, are you sure it has a value set? and Seconly try using.SaveAs Filename:="C:UserskakkaDesktopSymbolData" & cell.Value2 & "-v1.xls", FileFormat:=56– Tom
Nov 28 '18 at 14:30
What error do you get when it "fails on the following code line arbitrarily"?
– Comintern
Nov 28 '18 at 14:42
1
wsI.Range("A1:H1").Copyis a huge waste of resources. Instead of copying the entire column, only copy the range with data in it. This may be causing an overload especially because there's a long loop. You can also make it more efficient by removing copy/paste technique and settingrange.value = range.value, directly.– Scott Holtzman
Nov 28 '18 at 14:53
@Tom your suggestion to add Value2 after Cell solves the problem. Please make it as an answer so that I may tick it. Thanks a lot.
– skkakkar
Nov 28 '18 at 14:54
@skkakkar Glad it worked - added
– Tom
Nov 28 '18 at 14:57