Excel VBA non-english character support for reading files
I am using this piece of code for seperating sheets to single files:
Sub Splitbook()
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
But I get these two errors respectively when I run the code:
and that file ".VB5052.tmp" is being created at run time.
I figured out that this error occurs when my path have non-english characters. So, how to configure VBA in order to support non-english characters?
By the way, I have many different paths and I am doing this seperation most times.
This Path didn't work: "D:Yeni Klasör"
However, when I remove the "ö" character: "D:Yeni" this works. My sheet names include non-english characters but it is not the error. Just editing the path works.
excel vba utf-8 character-encoding non-english
edited Nov 26 '18 at 7:16
Pᴇʜ
21.8k42750
asked Nov 25 '18 at 21:19
ninbitninbit
178114
|
show 7 more comments
I am using this piece of code for seperating sheets to single files:
Sub Splitbook()
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
But I get these two errors respectively when I run the code:
and that file ".VB5052.tmp" is being created at run time.
I figured out that this error occurs when my path have non-english characters. So, how to configure VBA in order to support non-english characters?
By the way, I have many different paths and I am doing this seperation most times.
This Path didn't work: "D:Yeni Klasör"
However, when I remove the "ö" character: "D:Yeni" this works. My sheet names include non-english characters but it is not the error. Just editing the path works.
excel vba utf-8 character-encoding non-english
edited Nov 26 '18 at 7:16
Pᴇʜ
21.8k42750
asked Nov 25 '18 at 21:19
ninbitninbit
178114
Maybe the issue is at the OS level, what kind of special characters have you?
– Pragmateek
Nov 25 '18 at 21:36
Could you please edit your question and add sample sheet names pasted as text (not image please).
– Ahmed AU
Nov 26 '18 at 0:48
stackoverflow.com/questions/50341330/… ?
– Michal Rosa
Nov 26 '18 at 3:42
updated the question
– ninbit
Nov 26 '18 at 4:57
I bet it is not theöthat causes the issue but the space in the path. Try to enclose your path in double quotes""likeFilename:="""" & xPath & "" & xWs.Name & ".xlsx"""
– Pᴇʜ
Nov 26 '18 at 7:19
|
show 7 more comments
I am using this piece of code for seperating sheets to single files:
Sub Splitbook()
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
But I get these two errors respectively when I run the code:
and that file ".VB5052.tmp" is being created at run time.
I figured out that this error occurs when my path have non-english characters. So, how to configure VBA in order to support non-english characters?
By the way, I have many different paths and I am doing this seperation most times.
This Path didn't work: "D:Yeni Klasör"
However, when I remove the "ö" character: "D:Yeni" this works. My sheet names include non-english characters but it is not the error. Just editing the path works.
excel vba utf-8 character-encoding non-english
edited Nov 26 '18 at 7:16
Pᴇʜ
21.8k42750
asked Nov 25 '18 at 21:19
ninbitninbit
178114
I am using this piece of code for seperating sheets to single files:
Sub Splitbook()
Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
xWs.Copy
Application.ActiveWorkbook.SaveAs Filename:=xPath & "" & xWs.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
But I get these two errors respectively when I run the code:
and that file ".VB5052.tmp" is being created at run time.
I figured out that this error occurs when my path have non-english characters. So, how to configure VBA in order to support non-english characters?
By the way, I have many different paths and I am doing this seperation most times.
This Path didn't work: "D:Yeni Klasör"
However, when I remove the "ö" character: "D:Yeni" this works. My sheet names include non-english characters but it is not the error. Just editing the path works.
excel vba utf-8 character-encoding non-english
excel vba utf-8 character-encoding non-english
edited Nov 26 '18 at 7:16
Pᴇʜ
21.8k42750
asked Nov 25 '18 at 21:19
ninbitninbit
178114
edited Nov 26 '18 at 7:16
Pᴇʜ
21.8k42750
asked Nov 25 '18 at 21:19
ninbitninbit
178114
edited Nov 26 '18 at 7:16
Pᴇʜ
21.8k42750
edited Nov 26 '18 at 7:16
Pᴇʜ
21.8k42750
edited Nov 26 '18 at 7:16
Pᴇʜ
21.8k42750
21.8k42750
asked Nov 25 '18 at 21:19
ninbitninbit
178114
asked Nov 25 '18 at 21:19
ninbitninbit
178114
asked Nov 25 '18 at 21:19
ninbitninbit
178114
178114
Maybe the issue is at the OS level, what kind of special characters have you?
– Pragmateek
Nov 25 '18 at 21:36
Could you please edit your question and add sample sheet names pasted as text (not image please).
– Ahmed AU
Nov 26 '18 at 0:48
stackoverflow.com/questions/50341330/… ?
– Michal Rosa
Nov 26 '18 at 3:42
updated the question
– ninbit
Nov 26 '18 at 4:57
I bet it is not theöthat causes the issue but the space in the path. Try to enclose your path in double quotes""likeFilename:="""" & xPath & "" & xWs.Name & ".xlsx"""
– Pᴇʜ
Nov 26 '18 at 7:19
|
show 7 more comments
Maybe the issue is at the OS level, what kind of special characters have you?
– Pragmateek
Nov 25 '18 at 21:36
Could you please edit your question and add sample sheet names pasted as text (not image please).
– Ahmed AU
Nov 26 '18 at 0:48
stackoverflow.com/questions/50341330/… ?
– Michal Rosa
Nov 26 '18 at 3:42
updated the question
– ninbit
Nov 26 '18 at 4:57
I bet it is not theöthat causes the issue but the space in the path. Try to enclose your path in double quotes""likeFilename:="""" & xPath & "" & xWs.Name & ".xlsx"""
– Pᴇʜ
Nov 26 '18 at 7:19
Maybe the issue is at the OS level, what kind of special characters have you?
– Pragmateek
Nov 25 '18 at 21:36
Maybe the issue is at the OS level, what kind of special characters have you?
– Pragmateek
Nov 25 '18 at 21:36
Could you please edit your question and add sample sheet names pasted as text (not image please).
– Ahmed AU
Nov 26 '18 at 0:48
Could you please edit your question and add sample sheet names pasted as text (not image please).
– Ahmed AU
Nov 26 '18 at 0:48
stackoverflow.com/questions/50341330/… ?
– Michal Rosa
Nov 26 '18 at 3:42
stackoverflow.com/questions/50341330/… ?
– Michal Rosa
Nov 26 '18 at 3:42
updated the question
– ninbit
Nov 26 '18 at 4:57
updated the question
– ninbit
Nov 26 '18 at 4:57
I bet it is not the
ö that causes the issue but the space in the path. Try to enclose your path in double quotes "" like Filename:="""" & xPath & "" & xWs.Name & ".xlsx"""– Pᴇʜ
Nov 26 '18 at 7:19
I bet it is not the
ö that causes the issue but the space in the path. Try to enclose your path in double quotes "" like Filename:="""" & xPath & "" & xWs.Name & ".xlsx"""– Pᴇʜ
Nov 26 '18 at 7:19
|
show 7 more comments
1 Answer
1
active
oldest
votes
The solution is just controlling the system locale on windows like that:
- Go to Control Panel,
- Click Clock, Language and Region
- Windows 10, Windows 8: Click Region
Windows 7: Click Region and Language
Windows XP: Click Regional and Language Options
The Region and Language options dialog appears. - Click the Administrative tab
On Windows XP, click the Advanced tab
If there is no Advanced tab, then you are not logged in with administrative privileges. - Under the Language for non-Unicode programs section, click Change system locale and select the desired language.
- Click OK
- Restart the computer to apply the change.
In the 5th step, choose the language that you need (i.e. the language that has your character). Otherwise your path will contain some different characters which cause errors at run time.
answered Nov 26 '18 at 20:02
ninbitninbit
178114
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%2f53472094%2fexcel-vba-non-english-character-support-for-reading-files%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
The solution is just controlling the system locale on windows like that:
- Go to Control Panel,
- Click Clock, Language and Region
- Windows 10, Windows 8: Click Region
Windows 7: Click Region and Language
Windows XP: Click Regional and Language Options
The Region and Language options dialog appears. - Click the Administrative tab
On Windows XP, click the Advanced tab
If there is no Advanced tab, then you are not logged in with administrative privileges. - Under the Language for non-Unicode programs section, click Change system locale and select the desired language.
- Click OK
- Restart the computer to apply the change.
In the 5th step, choose the language that you need (i.e. the language that has your character). Otherwise your path will contain some different characters which cause errors at run time.
answered Nov 26 '18 at 20:02
ninbitninbit
178114
add a comment |
The solution is just controlling the system locale on windows like that:
- Go to Control Panel,
- Click Clock, Language and Region
- Windows 10, Windows 8: Click Region
Windows 7: Click Region and Language
Windows XP: Click Regional and Language Options
The Region and Language options dialog appears. - Click the Administrative tab
On Windows XP, click the Advanced tab
If there is no Advanced tab, then you are not logged in with administrative privileges. - Under the Language for non-Unicode programs section, click Change system locale and select the desired language.
- Click OK
- Restart the computer to apply the change.
In the 5th step, choose the language that you need (i.e. the language that has your character). Otherwise your path will contain some different characters which cause errors at run time.
answered Nov 26 '18 at 20:02
ninbitninbit
178114
add a comment |
The solution is just controlling the system locale on windows like that:
- Go to Control Panel,
- Click Clock, Language and Region
- Windows 10, Windows 8: Click Region
Windows 7: Click Region and Language
Windows XP: Click Regional and Language Options
The Region and Language options dialog appears. - Click the Administrative tab
On Windows XP, click the Advanced tab
If there is no Advanced tab, then you are not logged in with administrative privileges. - Under the Language for non-Unicode programs section, click Change system locale and select the desired language.
- Click OK
- Restart the computer to apply the change.
In the 5th step, choose the language that you need (i.e. the language that has your character). Otherwise your path will contain some different characters which cause errors at run time.
answered Nov 26 '18 at 20:02
ninbitninbit
178114
The solution is just controlling the system locale on windows like that:
- Go to Control Panel,
- Click Clock, Language and Region
- Windows 10, Windows 8: Click Region
Windows 7: Click Region and Language
Windows XP: Click Regional and Language Options
The Region and Language options dialog appears. - Click the Administrative tab
On Windows XP, click the Advanced tab
If there is no Advanced tab, then you are not logged in with administrative privileges. - Under the Language for non-Unicode programs section, click Change system locale and select the desired language.
- Click OK
- Restart the computer to apply the change.
In the 5th step, choose the language that you need (i.e. the language that has your character). Otherwise your path will contain some different characters which cause errors at run time.
answered Nov 26 '18 at 20:02
ninbitninbit
178114
answered Nov 26 '18 at 20:02
ninbitninbit
178114
answered Nov 26 '18 at 20:02
ninbitninbit
178114
answered Nov 26 '18 at 20:02
ninbitninbit
178114
178114
add a comment |
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%2f53472094%2fexcel-vba-non-english-character-support-for-reading-files%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
Maybe the issue is at the OS level, what kind of special characters have you?
– Pragmateek
Nov 25 '18 at 21:36
Could you please edit your question and add sample sheet names pasted as text (not image please).
– Ahmed AU
Nov 26 '18 at 0:48
stackoverflow.com/questions/50341330/… ?
– Michal Rosa
Nov 26 '18 at 3:42
updated the question
– ninbit
Nov 26 '18 at 4:57
I bet it is not the
öthat causes the issue but the space in the path. Try to enclose your path in double quotes""likeFilename:="""" & xPath & "" & xWs.Name & ".xlsx"""– Pᴇʜ
Nov 26 '18 at 7:19