Save Excel Sheet as CSV in UTF-8 Using VBA
0
I'm working on the following macro in excel, which loops through all of the Worksheets in a Workbook and saves each to its own .csv file. The issue is that I need it to save as a .csv encoded with UTF-8. I read that Excel's default encoding for .csv is ANSI, which won't work for me because I have some files with special characters in them. Does anyone know how I can go about this?
The macro:
Public Sub ExportSheetsToCSV()
Dim wsExport As Worksheet
Dim wbkExport As Workbook
For Each wsExport In Worksheets
wsExport.Select
nm = wsExport.Name
If Not IsActiveSheetEmpty() Then
ActiveSheet.SaveAs fileName:="H:CSV_Split_Exports" & nm, FileFormat:=xlCSV
Application.DisplayAlerts = True
End If
Next wsExport
End Sub
vba csv microsoft-excel-2016
asked Jul 25 '17 at 18:47
Jason BlockloveJason Blocklove
114
add a comment |
0
I'm working on the following macro in excel, which loops through all of the Worksheets in a Workbook and saves each to its own .csv file. The issue is that I need it to save as a .csv encoded with UTF-8. I read that Excel's default encoding for .csv is ANSI, which won't work for me because I have some files with special characters in them. Does anyone know how I can go about this?
The macro:
Public Sub ExportSheetsToCSV()
Dim wsExport As Worksheet
Dim wbkExport As Workbook
For Each wsExport In Worksheets
wsExport.Select
nm = wsExport.Name
If Not IsActiveSheetEmpty() Then
ActiveSheet.SaveAs fileName:="H:CSV_Split_Exports" & nm, FileFormat:=xlCSV
Application.DisplayAlerts = True
End If
Next wsExport
End Sub
vba csv microsoft-excel-2016
asked Jul 25 '17 at 18:47
Jason BlockloveJason Blocklove
114
add a comment |
0
0
0
I'm working on the following macro in excel, which loops through all of the Worksheets in a Workbook and saves each to its own .csv file. The issue is that I need it to save as a .csv encoded with UTF-8. I read that Excel's default encoding for .csv is ANSI, which won't work for me because I have some files with special characters in them. Does anyone know how I can go about this?
The macro:
Public Sub ExportSheetsToCSV()
Dim wsExport As Worksheet
Dim wbkExport As Workbook
For Each wsExport In Worksheets
wsExport.Select
nm = wsExport.Name
If Not IsActiveSheetEmpty() Then
ActiveSheet.SaveAs fileName:="H:CSV_Split_Exports" & nm, FileFormat:=xlCSV
Application.DisplayAlerts = True
End If
Next wsExport
End Sub
vba csv microsoft-excel-2016
asked Jul 25 '17 at 18:47
Jason BlockloveJason Blocklove
114
I'm working on the following macro in excel, which loops through all of the Worksheets in a Workbook and saves each to its own .csv file. The issue is that I need it to save as a .csv encoded with UTF-8. I read that Excel's default encoding for .csv is ANSI, which won't work for me because I have some files with special characters in them. Does anyone know how I can go about this?
The macro:
Public Sub ExportSheetsToCSV()
Dim wsExport As Worksheet
Dim wbkExport As Workbook
For Each wsExport In Worksheets
wsExport.Select
nm = wsExport.Name
If Not IsActiveSheetEmpty() Then
ActiveSheet.SaveAs fileName:="H:CSV_Split_Exports" & nm, FileFormat:=xlCSV
Application.DisplayAlerts = True
End If
Next wsExport
End Sub
vba csv microsoft-excel-2016
vba csv microsoft-excel-2016
asked Jul 25 '17 at 18:47
Jason BlockloveJason Blocklove
114
asked Jul 25 '17 at 18:47
Jason BlockloveJason Blocklove
114
asked Jul 25 '17 at 18:47
Jason BlockloveJason Blocklove
114
asked Jul 25 '17 at 18:47
Jason BlockloveJason Blocklove
114
asked Jul 25 '17 at 18:47
Jason BlockloveJason Blocklove
114
114
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
The "easy way" is to save as "Unicode Text." This will be UTF-16, tab delimited format, rather than comma delimited.
Most software import processes allows you to specify the delimiter when importing the data, but if your particular application does not, you can post-process the file with a find+replace to change the tab to a comma.
answered Jul 25 '17 at 20:59
YorikYorik
2,6841610
Is there a "hard way?" The import software I'm using doesn't allow me to select my delimiter and the whole goal of me writing this macro was to avoid things like individual post-processing. I don't mind trying to implement a more complex macro if it gets me where I need to go.
– Jason Blocklove
Jul 27 '17 at 13:48
you can post-process the file in VBA after you export it: open it, read lines, process the tabs, write to new file. Need to use unicode-safe string functions.
– Yorik
Jul 27 '17 at 13:59
Oh, okay. I understand what you're saying. Is there any way you could help me with the VBA for that? I'm just learning how to use it and I feel like that goes a bit over my head as of now.
– Jason Blocklove
Jul 28 '17 at 14:19
You need to open two files, one to read, one to write. You then read a line to a string variable, usereplace()on the variable, then write to the second file. You would use something like a "while not end-of file loop". Check the VBA documentation also, but here is a VB Script version of something without the loop: stackoverflow.com/questions/1410334/…
– Yorik
Jul 28 '17 at 15:08
You will find a lot of simple input/output text file examples, some even on MSDN. It isn't very complicated. Once you get something that doesn't work (!), if you get stuck, check out stackoverflow. The best way is to save an example file, and then play with it as a function with 2 parameters (infile, outfile), then once you get that done, insert the call to the function into the loop you posted above, immediately before yourNext wsExport
– Yorik
Jul 28 '17 at 15:11
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1234377%2fsave-excel-sheet-as-csv-in-utf-8-using-vba%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
The "easy way" is to save as "Unicode Text." This will be UTF-16, tab delimited format, rather than comma delimited.
Most software import processes allows you to specify the delimiter when importing the data, but if your particular application does not, you can post-process the file with a find+replace to change the tab to a comma.
answered Jul 25 '17 at 20:59
YorikYorik
2,6841610
Is there a "hard way?" The import software I'm using doesn't allow me to select my delimiter and the whole goal of me writing this macro was to avoid things like individual post-processing. I don't mind trying to implement a more complex macro if it gets me where I need to go.
– Jason Blocklove
Jul 27 '17 at 13:48
you can post-process the file in VBA after you export it: open it, read lines, process the tabs, write to new file. Need to use unicode-safe string functions.
– Yorik
Jul 27 '17 at 13:59
Oh, okay. I understand what you're saying. Is there any way you could help me with the VBA for that? I'm just learning how to use it and I feel like that goes a bit over my head as of now.
– Jason Blocklove
Jul 28 '17 at 14:19
You need to open two files, one to read, one to write. You then read a line to a string variable, usereplace()on the variable, then write to the second file. You would use something like a "while not end-of file loop". Check the VBA documentation also, but here is a VB Script version of something without the loop: stackoverflow.com/questions/1410334/…
– Yorik
Jul 28 '17 at 15:08
You will find a lot of simple input/output text file examples, some even on MSDN. It isn't very complicated. Once you get something that doesn't work (!), if you get stuck, check out stackoverflow. The best way is to save an example file, and then play with it as a function with 2 parameters (infile, outfile), then once you get that done, insert the call to the function into the loop you posted above, immediately before yourNext wsExport
– Yorik
Jul 28 '17 at 15:11
add a comment |
0
The "easy way" is to save as "Unicode Text." This will be UTF-16, tab delimited format, rather than comma delimited.
Most software import processes allows you to specify the delimiter when importing the data, but if your particular application does not, you can post-process the file with a find+replace to change the tab to a comma.
answered Jul 25 '17 at 20:59
YorikYorik
2,6841610
Is there a "hard way?" The import software I'm using doesn't allow me to select my delimiter and the whole goal of me writing this macro was to avoid things like individual post-processing. I don't mind trying to implement a more complex macro if it gets me where I need to go.
– Jason Blocklove
Jul 27 '17 at 13:48
you can post-process the file in VBA after you export it: open it, read lines, process the tabs, write to new file. Need to use unicode-safe string functions.
– Yorik
Jul 27 '17 at 13:59
Oh, okay. I understand what you're saying. Is there any way you could help me with the VBA for that? I'm just learning how to use it and I feel like that goes a bit over my head as of now.
– Jason Blocklove
Jul 28 '17 at 14:19
You need to open two files, one to read, one to write. You then read a line to a string variable, usereplace()on the variable, then write to the second file. You would use something like a "while not end-of file loop". Check the VBA documentation also, but here is a VB Script version of something without the loop: stackoverflow.com/questions/1410334/…
– Yorik
Jul 28 '17 at 15:08
You will find a lot of simple input/output text file examples, some even on MSDN. It isn't very complicated. Once you get something that doesn't work (!), if you get stuck, check out stackoverflow. The best way is to save an example file, and then play with it as a function with 2 parameters (infile, outfile), then once you get that done, insert the call to the function into the loop you posted above, immediately before yourNext wsExport
– Yorik
Jul 28 '17 at 15:11
add a comment |
0
0
0
The "easy way" is to save as "Unicode Text." This will be UTF-16, tab delimited format, rather than comma delimited.
Most software import processes allows you to specify the delimiter when importing the data, but if your particular application does not, you can post-process the file with a find+replace to change the tab to a comma.
answered Jul 25 '17 at 20:59
YorikYorik
2,6841610
The "easy way" is to save as "Unicode Text." This will be UTF-16, tab delimited format, rather than comma delimited.
Most software import processes allows you to specify the delimiter when importing the data, but if your particular application does not, you can post-process the file with a find+replace to change the tab to a comma.
answered Jul 25 '17 at 20:59
YorikYorik
2,6841610
answered Jul 25 '17 at 20:59
YorikYorik
2,6841610
answered Jul 25 '17 at 20:59
YorikYorik
2,6841610
answered Jul 25 '17 at 20:59
YorikYorik
2,6841610
2,6841610
Is there a "hard way?" The import software I'm using doesn't allow me to select my delimiter and the whole goal of me writing this macro was to avoid things like individual post-processing. I don't mind trying to implement a more complex macro if it gets me where I need to go.
– Jason Blocklove
Jul 27 '17 at 13:48
you can post-process the file in VBA after you export it: open it, read lines, process the tabs, write to new file. Need to use unicode-safe string functions.
– Yorik
Jul 27 '17 at 13:59
Oh, okay. I understand what you're saying. Is there any way you could help me with the VBA for that? I'm just learning how to use it and I feel like that goes a bit over my head as of now.
– Jason Blocklove
Jul 28 '17 at 14:19
You need to open two files, one to read, one to write. You then read a line to a string variable, usereplace()on the variable, then write to the second file. You would use something like a "while not end-of file loop". Check the VBA documentation also, but here is a VB Script version of something without the loop: stackoverflow.com/questions/1410334/…
– Yorik
Jul 28 '17 at 15:08
You will find a lot of simple input/output text file examples, some even on MSDN. It isn't very complicated. Once you get something that doesn't work (!), if you get stuck, check out stackoverflow. The best way is to save an example file, and then play with it as a function with 2 parameters (infile, outfile), then once you get that done, insert the call to the function into the loop you posted above, immediately before yourNext wsExport
– Yorik
Jul 28 '17 at 15:11
add a comment |
Is there a "hard way?" The import software I'm using doesn't allow me to select my delimiter and the whole goal of me writing this macro was to avoid things like individual post-processing. I don't mind trying to implement a more complex macro if it gets me where I need to go.
– Jason Blocklove
Jul 27 '17 at 13:48
you can post-process the file in VBA after you export it: open it, read lines, process the tabs, write to new file. Need to use unicode-safe string functions.
– Yorik
Jul 27 '17 at 13:59
Oh, okay. I understand what you're saying. Is there any way you could help me with the VBA for that? I'm just learning how to use it and I feel like that goes a bit over my head as of now.
– Jason Blocklove
Jul 28 '17 at 14:19
You need to open two files, one to read, one to write. You then read a line to a string variable, usereplace()on the variable, then write to the second file. You would use something like a "while not end-of file loop". Check the VBA documentation also, but here is a VB Script version of something without the loop: stackoverflow.com/questions/1410334/…
– Yorik
Jul 28 '17 at 15:08
You will find a lot of simple input/output text file examples, some even on MSDN. It isn't very complicated. Once you get something that doesn't work (!), if you get stuck, check out stackoverflow. The best way is to save an example file, and then play with it as a function with 2 parameters (infile, outfile), then once you get that done, insert the call to the function into the loop you posted above, immediately before yourNext wsExport
– Yorik
Jul 28 '17 at 15:11
Is there a "hard way?" The import software I'm using doesn't allow me to select my delimiter and the whole goal of me writing this macro was to avoid things like individual post-processing. I don't mind trying to implement a more complex macro if it gets me where I need to go.
– Jason Blocklove
Jul 27 '17 at 13:48
Is there a "hard way?" The import software I'm using doesn't allow me to select my delimiter and the whole goal of me writing this macro was to avoid things like individual post-processing. I don't mind trying to implement a more complex macro if it gets me where I need to go.
– Jason Blocklove
Jul 27 '17 at 13:48
you can post-process the file in VBA after you export it: open it, read lines, process the tabs, write to new file. Need to use unicode-safe string functions.
– Yorik
Jul 27 '17 at 13:59
you can post-process the file in VBA after you export it: open it, read lines, process the tabs, write to new file. Need to use unicode-safe string functions.
– Yorik
Jul 27 '17 at 13:59
Oh, okay. I understand what you're saying. Is there any way you could help me with the VBA for that? I'm just learning how to use it and I feel like that goes a bit over my head as of now.
– Jason Blocklove
Jul 28 '17 at 14:19
Oh, okay. I understand what you're saying. Is there any way you could help me with the VBA for that? I'm just learning how to use it and I feel like that goes a bit over my head as of now.
– Jason Blocklove
Jul 28 '17 at 14:19
You need to open two files, one to read, one to write. You then read a line to a string variable, use
replace() on the variable, then write to the second file. You would use something like a "while not end-of file loop". Check the VBA documentation also, but here is a VB Script version of something without the loop: stackoverflow.com/questions/1410334/…– Yorik
Jul 28 '17 at 15:08
You need to open two files, one to read, one to write. You then read a line to a string variable, use
replace() on the variable, then write to the second file. You would use something like a "while not end-of file loop". Check the VBA documentation also, but here is a VB Script version of something without the loop: stackoverflow.com/questions/1410334/…– Yorik
Jul 28 '17 at 15:08
You will find a lot of simple input/output text file examples, some even on MSDN. It isn't very complicated. Once you get something that doesn't work (!), if you get stuck, check out stackoverflow. The best way is to save an example file, and then play with it as a function with 2 parameters (infile, outfile), then once you get that done, insert the call to the function into the loop you posted above, immediately before your
Next wsExport– Yorik
Jul 28 '17 at 15:11
You will find a lot of simple input/output text file examples, some even on MSDN. It isn't very complicated. Once you get something that doesn't work (!), if you get stuck, check out stackoverflow. The best way is to save an example file, and then play with it as a function with 2 parameters (infile, outfile), then once you get that done, insert the call to the function into the loop you posted above, immediately before your
Next wsExport– Yorik
Jul 28 '17 at 15:11
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1234377%2fsave-excel-sheet-as-csv-in-utf-8-using-vba%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
