Excel VBA, sorting, saving, closing, then opening
I have two Excel files, file1.xlsm and file2.xlsm; I have a data table in one, and soemthing else in the other. In the second file I also have a button which opens the first file.
In the first file I have a button which sorts all data using column A as the key and a second button to save and exit.
My code for sorting:
Private Sub CommandButton2_Click()
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet.Sort
.SortFields.Add Key:=Range("A3"), Order:=xlAscending
.SetRange Range("A3:AF" & lRow)
.Header = xlNo
.Apply
End With
End Sub
The problem is that after I sort, save and exit I cannot re-open the same workbook through VBA (the button does nothing); I can click on the file, then it opens but gives me an error that there were problems, yet Excel can recover. The error in error log:
Errors were detected in file 'C:file1.xlsm'
Removed Records: Sorting from
/xl/worksheets/sheet1.xml part
The button to save is just closing the workbook with SaveChanges:=True.
Why is this happening and how can it be rectified?
excel vba sorting recover
edited Nov 27 '18 at 11:52
Cindy Meister
15.5k102235
asked Nov 27 '18 at 11:02
Ignas AnfalovasIgnas Anfalovas
1
add a comment |
I have two Excel files, file1.xlsm and file2.xlsm; I have a data table in one, and soemthing else in the other. In the second file I also have a button which opens the first file.
In the first file I have a button which sorts all data using column A as the key and a second button to save and exit.
My code for sorting:
Private Sub CommandButton2_Click()
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet.Sort
.SortFields.Add Key:=Range("A3"), Order:=xlAscending
.SetRange Range("A3:AF" & lRow)
.Header = xlNo
.Apply
End With
End Sub
The problem is that after I sort, save and exit I cannot re-open the same workbook through VBA (the button does nothing); I can click on the file, then it opens but gives me an error that there were problems, yet Excel can recover. The error in error log:
Errors were detected in file 'C:file1.xlsm'
Removed Records: Sorting from
/xl/worksheets/sheet1.xml part
The button to save is just closing the workbook with SaveChanges:=True.
Why is this happening and how can it be rectified?
excel vba sorting recover
edited Nov 27 '18 at 11:52
Cindy Meister
15.5k102235
asked Nov 27 '18 at 11:02
Ignas AnfalovasIgnas Anfalovas
1
This "Errors were detected in file" means that your file is corrupt. Try saving it in the binary.xlsbformat (this might fix it) and then save at again in XML format.xlsm. I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files).
– Pᴇʜ
Nov 27 '18 at 11:06
wouldn't it delete all my macros and buttons inside? I will try and get back with results.
– Ignas Anfalovas
Nov 27 '18 at 11:11
No, the binary xlsb supports macros too.
– Pᴇʜ
Nov 27 '18 at 11:17
add a comment |
I have two Excel files, file1.xlsm and file2.xlsm; I have a data table in one, and soemthing else in the other. In the second file I also have a button which opens the first file.
In the first file I have a button which sorts all data using column A as the key and a second button to save and exit.
My code for sorting:
Private Sub CommandButton2_Click()
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet.Sort
.SortFields.Add Key:=Range("A3"), Order:=xlAscending
.SetRange Range("A3:AF" & lRow)
.Header = xlNo
.Apply
End With
End Sub
The problem is that after I sort, save and exit I cannot re-open the same workbook through VBA (the button does nothing); I can click on the file, then it opens but gives me an error that there were problems, yet Excel can recover. The error in error log:
Errors were detected in file 'C:file1.xlsm'
Removed Records: Sorting from
/xl/worksheets/sheet1.xml part
The button to save is just closing the workbook with SaveChanges:=True.
Why is this happening and how can it be rectified?
excel vba sorting recover
edited Nov 27 '18 at 11:52
Cindy Meister
15.5k102235
asked Nov 27 '18 at 11:02
Ignas AnfalovasIgnas Anfalovas
1
I have two Excel files, file1.xlsm and file2.xlsm; I have a data table in one, and soemthing else in the other. In the second file I also have a button which opens the first file.
In the first file I have a button which sorts all data using column A as the key and a second button to save and exit.
My code for sorting:
Private Sub CommandButton2_Click()
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet.Sort
.SortFields.Add Key:=Range("A3"), Order:=xlAscending
.SetRange Range("A3:AF" & lRow)
.Header = xlNo
.Apply
End With
End Sub
The problem is that after I sort, save and exit I cannot re-open the same workbook through VBA (the button does nothing); I can click on the file, then it opens but gives me an error that there were problems, yet Excel can recover. The error in error log:
Errors were detected in file 'C:file1.xlsm'
Removed Records: Sorting from
/xl/worksheets/sheet1.xml part
The button to save is just closing the workbook with SaveChanges:=True.
Why is this happening and how can it be rectified?
excel vba sorting recover
excel vba sorting recover
edited Nov 27 '18 at 11:52
Cindy Meister
15.5k102235
asked Nov 27 '18 at 11:02
Ignas AnfalovasIgnas Anfalovas
1
edited Nov 27 '18 at 11:52
Cindy Meister
15.5k102235
asked Nov 27 '18 at 11:02
Ignas AnfalovasIgnas Anfalovas
1
edited Nov 27 '18 at 11:52
Cindy Meister
15.5k102235
edited Nov 27 '18 at 11:52
Cindy Meister
15.5k102235
edited Nov 27 '18 at 11:52
Cindy Meister
15.5k102235
15.5k102235
asked Nov 27 '18 at 11:02
Ignas AnfalovasIgnas Anfalovas
1
asked Nov 27 '18 at 11:02
Ignas AnfalovasIgnas Anfalovas
1
asked Nov 27 '18 at 11:02
Ignas AnfalovasIgnas Anfalovas
1
1
This "Errors were detected in file" means that your file is corrupt. Try saving it in the binary.xlsbformat (this might fix it) and then save at again in XML format.xlsm. I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files).
– Pᴇʜ
Nov 27 '18 at 11:06
wouldn't it delete all my macros and buttons inside? I will try and get back with results.
– Ignas Anfalovas
Nov 27 '18 at 11:11
No, the binary xlsb supports macros too.
– Pᴇʜ
Nov 27 '18 at 11:17
add a comment |
This "Errors were detected in file" means that your file is corrupt. Try saving it in the binary.xlsbformat (this might fix it) and then save at again in XML format.xlsm. I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files).
– Pᴇʜ
Nov 27 '18 at 11:06
wouldn't it delete all my macros and buttons inside? I will try and get back with results.
– Ignas Anfalovas
Nov 27 '18 at 11:11
No, the binary xlsb supports macros too.
– Pᴇʜ
Nov 27 '18 at 11:17
This "Errors were detected in file" means that your file is corrupt. Try saving it in the binary
.xlsb format (this might fix it) and then save at again in XML format .xlsm. I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files).– Pᴇʜ
Nov 27 '18 at 11:06
This "Errors were detected in file" means that your file is corrupt. Try saving it in the binary
.xlsb format (this might fix it) and then save at again in XML format .xlsm. I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files).– Pᴇʜ
Nov 27 '18 at 11:06
wouldn't it delete all my macros and buttons inside? I will try and get back with results.
– Ignas Anfalovas
Nov 27 '18 at 11:11
wouldn't it delete all my macros and buttons inside? I will try and get back with results.
– Ignas Anfalovas
Nov 27 '18 at 11:11
No, the binary xlsb supports macros too.
– Pᴇʜ
Nov 27 '18 at 11:17
No, the binary xlsb supports macros too.
– Pᴇʜ
Nov 27 '18 at 11:17
add a comment |
1 Answer
1
active
oldest
votes
This Error
Errors were detected in file
means that your file is corrupt.
In many cases converting the file into the binary .xlsb format repairs the file. After that you can save at again in XML format .xlsm.
Nevertheless, I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files too).
answered Nov 27 '18 at 12:09
PᴇʜPᴇʜ
23.6k62951
Sorry, but it turns out not to be the best approach, as if I use the VBA to sort the data the way I showed above, both xlsm and xlsb get corrupted, although not always.
– Ignas Anfalovas
Nov 27 '18 at 13:11
Try if it happen with a completely new file. Saving in a different format can help but sometimes the file stays corrupt. Try it with a completely new file that is not corrupt.
– Pᴇʜ
Nov 27 '18 at 13:22
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
});
}
});
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%2f53498231%2fexcel-vba-sorting-saving-closing-then-opening%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
This Error
Errors were detected in file
means that your file is corrupt.
In many cases converting the file into the binary .xlsb format repairs the file. After that you can save at again in XML format .xlsm.
Nevertheless, I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files too).
answered Nov 27 '18 at 12:09
PᴇʜPᴇʜ
23.6k62951
Sorry, but it turns out not to be the best approach, as if I use the VBA to sort the data the way I showed above, both xlsm and xlsb get corrupted, although not always.
– Ignas Anfalovas
Nov 27 '18 at 13:11
Try if it happen with a completely new file. Saving in a different format can help but sometimes the file stays corrupt. Try it with a completely new file that is not corrupt.
– Pᴇʜ
Nov 27 '18 at 13:22
add a comment |
This Error
Errors were detected in file
means that your file is corrupt.
In many cases converting the file into the binary .xlsb format repairs the file. After that you can save at again in XML format .xlsm.
Nevertheless, I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files too).
answered Nov 27 '18 at 12:09
PᴇʜPᴇʜ
23.6k62951
Sorry, but it turns out not to be the best approach, as if I use the VBA to sort the data the way I showed above, both xlsm and xlsb get corrupted, although not always.
– Ignas Anfalovas
Nov 27 '18 at 13:11
Try if it happen with a completely new file. Saving in a different format can help but sometimes the file stays corrupt. Try it with a completely new file that is not corrupt.
– Pᴇʜ
Nov 27 '18 at 13:22
add a comment |
This Error
Errors were detected in file
means that your file is corrupt.
In many cases converting the file into the binary .xlsb format repairs the file. After that you can save at again in XML format .xlsm.
Nevertheless, I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files too).
answered Nov 27 '18 at 12:09
PᴇʜPᴇʜ
23.6k62951
This Error
Errors were detected in file
means that your file is corrupt.
In many cases converting the file into the binary .xlsb format repairs the file. After that you can save at again in XML format .xlsm.
Nevertheless, I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files too).
answered Nov 27 '18 at 12:09
PᴇʜPᴇʜ
23.6k62951
answered Nov 27 '18 at 12:09
PᴇʜPᴇʜ
23.6k62951
answered Nov 27 '18 at 12:09
PᴇʜPᴇʜ
23.6k62951
answered Nov 27 '18 at 12:09
PᴇʜPᴇʜ
23.6k62951
23.6k62951
Sorry, but it turns out not to be the best approach, as if I use the VBA to sort the data the way I showed above, both xlsm and xlsb get corrupted, although not always.
– Ignas Anfalovas
Nov 27 '18 at 13:11
Try if it happen with a completely new file. Saving in a different format can help but sometimes the file stays corrupt. Try it with a completely new file that is not corrupt.
– Pᴇʜ
Nov 27 '18 at 13:22
add a comment |
Sorry, but it turns out not to be the best approach, as if I use the VBA to sort the data the way I showed above, both xlsm and xlsb get corrupted, although not always.
– Ignas Anfalovas
Nov 27 '18 at 13:11
Try if it happen with a completely new file. Saving in a different format can help but sometimes the file stays corrupt. Try it with a completely new file that is not corrupt.
– Pᴇʜ
Nov 27 '18 at 13:22
Sorry, but it turns out not to be the best approach, as if I use the VBA to sort the data the way I showed above, both xlsm and xlsb get corrupted, although not always.
– Ignas Anfalovas
Nov 27 '18 at 13:11
Sorry, but it turns out not to be the best approach, as if I use the VBA to sort the data the way I showed above, both xlsm and xlsb get corrupted, although not always.
– Ignas Anfalovas
Nov 27 '18 at 13:11
Try if it happen with a completely new file. Saving in a different format can help but sometimes the file stays corrupt. Try it with a completely new file that is not corrupt.
– Pᴇʜ
Nov 27 '18 at 13:22
Try if it happen with a completely new file. Saving in a different format can help but sometimes the file stays corrupt. Try it with a completely new file that is not corrupt.
– Pᴇʜ
Nov 27 '18 at 13:22
add a comment |
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%2f53498231%2fexcel-vba-sorting-saving-closing-then-opening%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
This "Errors were detected in file" means that your file is corrupt. Try saving it in the binary
.xlsbformat (this might fix it) and then save at again in XML format.xlsm. I often had issues with the XML format and file corruption. So for big data I recommend to use the binary format (it's a bit more efficient and produces smaller files).– Pᴇʜ
Nov 27 '18 at 11:06
wouldn't it delete all my macros and buttons inside? I will try and get back with results.
– Ignas Anfalovas
Nov 27 '18 at 11:11
No, the binary xlsb supports macros too.
– Pᴇʜ
Nov 27 '18 at 11:17