Special characters

[Mr. T]

We collect data from a form available on the Internet. This form stores the
submitted data into a .csv file. When we open this file with Excel (Excel
2004 v. 11.1 for Mac) the European special characters shows as different
symbols. The characters display nicely in the .csv file itself. When opening
the same file on a PC running Open Office there's an option to choose what
character set we want to use when importing the .csv file. Open Office
suggest using "Western Europe (Windows-1252/WinLatin 1)" which works
perfectly. But I need it to work with a Mac running OS-X. I can't find any
place to change the character set used when importing the file to Excel
running on Mac.

I don't know how this will display on everyone's screens, but here's
examples on some of the characters: "æ", "ø", "å". (There's only one
character within each "").

A solution to the problem, if no-one has any better suggestion, could
perhaps be to make a command to change whatever symbols (different
characters put together) that replaces the original character in all of the
document after it is imported. I have very little experience using MS Office
and don't know if that's even possible to make a "command" like this, - or
if it is possible, how to do it. This is a document meant to be updated and
imported daily.

Anyone have any suggestions on how to make it work? Or any ideas where to
ask?
Thanks,

 

[jpdphd]

Mr T,

1. What DO you see when opening the file in Mac Excel?
2. What font are you using in Mac Excel?
3. What do you see when opening the file in TextEdit (an application
that's on every Mac)?

jpdphd

 

[Mr. T]

Thanks for reply, jpdphd,

Mr T,

1. What DO you see when opening the file in Mac Excel?

The Mac is at the office so I can't tell you exact what it is I see, but I
guess I'm not far off when I say each character look something like this
"^¿?". This is special characters used with most font's I believe. I'll try
to drive by the office tomorrow and find out for sure if this is essential
information to find a solution.

2. What font are you using in Mac Excel?

I believe it's the standard font, which I believe is Arial 10 (like in Open
Office). But I'm sure the font is not the problem.

3. What do you see when opening the file in TextEdit (an application
that's on every Mac)?

I have to admit I didn't try that, and as said, unfortunately I don't have
the Mac where I am at the moment. But I have uploaded to the Internet a .csv
file including the characters that's making trouble. If you find the time,
please take a look at the file. In the web browser, everything looks like it
should (at least here it does), but if you save the file and open it in
Excel it's not the same.

Please see:
http://www.scantem.org/test.csv

And just to make sure it looks the same on your computer, here's a
screenshot of what it looks like here:
http://www.scantem.org/test.jpg

BTW, in HTML the character's are coded like this: æ ø å

Thanks again,

 

[jpdphd]

Mr T,

The problem seems to be that the file is created in a windows
environment. The coding of characters (beyond 128) is different between
windows and mac. See this web page for more details.
http://home.tiscali.nl/t876506/charsets.html

You can use the following Visual Basic macro to convert from windows to
mac format. You can also use include more conversions by adding "If ..
End If" statements.

If you didn't install Visual Basic, you will have to do it with the
original excel installation disk.

To install the macro:
Copy the code below (from Sub to End Sub)
Tools > Macro > Visual Basic Editor
In Projects window, click on VBAProject (Personal Macro Workbook)
the step above will make the macro available in all of your
spreadsheets
Insert > Module
Paste
Excel > Close and Return to Microsoft Excel

To run the macro,
Tools > Macro > Macros...

I hope this helps!

jpdphd
----

Sub Windows2Mac()
' converts certain windows characters to the equivalent macintosh
characters
' will convert characters within all selected cells
Dim Text
SourceRange = Selection
For Each i In Selection
Text = i.Value
Length = Len(Text)
For j = 1 To Length
If Asc(Mid(Text, j, j)) = 230 Then ' Ê to æ
Text = Mid(Text, 1, j - 1) + Chr(190) + Mid(Text, j + 1,
Length)
End If
If Asc(Mid(Text, j, j)) = 248 Then ' ¯ to ø
Text = Mid(Text, 1, j - 1) + Chr(191) + Mid(Text, j + 1,
Length)
End If
If Asc(Mid(Text, j, j)) = 229 Then ' Â to å
Text = Mid(Text, 1, j - 1) + Chr(140) + Mid(Text, j + 1,
Length)
End If
If Asc(Mid(Text, j, j)) = 198 Then ' ∆ to Æ
Text = Mid(Text, 1, j - 1) + Chr(174) + Mid(Text, j + 1,
Length)
End If
If Asc(Mid(Text, j, j)) = 216 Then ' ÿ to Ø
Text = Mid(Text, 1, j - 1) + Chr(175) + Mid(Text, j + 1,
Length)
End If
If Asc(Mid(Text, j, j)) = 197 Then ' ≈ to Å
Text = Mid(Text, 1, j - 1) + Chr(129) + Mid(Text, j + 1,
Length)
End If
Next j
i.Value = Text
Next i

End Sub

 

[jpdphd]

When my msg was uploaded, some <returns> were inserted. After pasting
the macro, move the word "characters" that is all alone on line 3, up
to line 2. Move the 6 occurrences of "Length)" to the previous lines.
jpdphd

 

[Mr. T]

Mr T,

The problem seems to be (..) I hope this helps!

Thank you very much, jpdphd, but I guess it won't help. I just spoke with MS
Office support and they told me that they was well aware of the problem, and
that it might (that is - perhaps!) will be fixed in the next version of Mac
Office.
SO... what do I do? I need the form to work. Not later on sometime, but now!
Do I trash her Mac and get her a new Windows PC or what? She's most familiar
with the Mac, and also prefers the Mac, but what can I do...? If it won't
work, it won't work. Right?

I know there must be a solution to this problem (if there's a problem
there's always a solution), - but I don't know where to look..... Anyone??
Thanks,

 

[Paul Berkowitz]

A solution to the problem, if no-one has any better suggestion, could
perhaps be to make a command to change whatever symbols (different
characters put together) that replaces the original character in all of the
document after it is imported. I have very little experience using MS Office
and don't know if that's even possible to make a "command" like this, - or
if it is possible, how to do it. This is a document meant to be updated and
imported daily.

Anyone have any suggestions on how to make it work? Or any ideas where to
ask?

I may have a way, using AppleScript. Could you please send one such file to
me (remove "spoof_" from my email address). I'll need to test it out.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[jpdphd]

Mr T,

Please try the macro I sent. It works for me.

jpdphd

 

[Mr. T]

Mr T,

Please try the macro I sent. It works for me.

jpdphd

Thanks again, jpdphd. I have now tested the macro, but unfortunately, it
doesn't work here...
When I run the macro I get the following error:

Compile error: Syntax error

When I click "OK" to that message, the macro opens up with the first line
marked yellow.
Please see screenshot: www.tcb.no/tmp/error.pdf

Is it something I've done wrong? Please advice.

Just to make sure it's not some version problems, here are my details:
MS Excel 2004 for Mac. Version 11.1 (040909)
Mac OS X version 10.3.9.

Thank you,

 

[jpdphd]

Mr T,

The text in red indicates that the line is not being understood. All of
those lines should be indented with 1 or 2 tabs. I think the tabs must
have gotten converted into spaces during copy & paste. Try deleting
those spaces. You can insert tabs instead, but the aren't necessary
(just helps you see the organization of the macro).

I'll keep my fingers crossed...

jpdphd

 

[Mr. T]

Mr T,

The text in red indicates that the line is not being understood. All
of those lines should be indented with 1 or 2 tabs. I think the tabs
must have gotten converted into spaces during copy & paste. Try
deleting those spaces. You can insert tabs instead, but the aren't
necessary (just helps you see the organization of the macro).

I'll keep my fingers crossed...

jpdphd

IT WORKS!! Thanks a lot!

BUT, there's still a few problems,

1. I can't find the VBAProject (Personal Macro Workbook) in the Projects
window. The project window looks like this:
http://www.tcb.no/tmp/vbaprojects.pdf
What I've done is open a (new) Excel document, and inserted the module, then
saved the file. Then it works. That means I have to open the new excel file,
THEN I have to open the .csv file, and only then the macro works with the
..csv file. If I don't open the excel file first the macro is not available
to run. (No macro's found).
It would be nice if all this extra click's could be avoided, and that we
could make a key-board short-cut to run the macro from the .csv file. Any
suggestions?

2. This is not related to the subject but I'll try anyway... The .csv file
is a file that collects data submitted from a form available on the
Internet. That is, the file is updated with new information every time
someone use this form. This happens many times every day, thus this file is
downloaded at least 4 - 6 times a day. When the person that edit all the
information get to work she download the fresh file. Then a couple of hours
later she needs an updated version and download this (edited) file once more
and save the file to her computer, either with a new filename or with the
same filename (the latter is supposed to overwrite the first file). But no
matter which of the mentioned alternatives she choose, the file is identical
to the first file. This even though the file is updated with new
information. How come?
The only way to get the updated file to display is to delete the first file
AND empty the trash bin, and then restart Excel, and then open up the file.
Then it works.

This works the way it should on another Mac, how come it doesn't work on
this? Something with some settings perhaps, or is it a more serious problem?
If I remember correctly the computer is a G4 Powerbook, 1Ghz, 1GB memory.

Thanks again,

 

[jpdphd]

Mr. T,

Sorry for the delay - I have been out of town.

1. Perhaps you have to do the following to get the Personal Macro
Workbook started.
a. View the macro and copy the text in between the Sub line and the End
Sub line.
b. Return to spreadsheet. Tools > Macro > Macros...
enter Windows2Mac into the name box and choose "Personal Macro
Workbook" in the pull down menu for "Macros in..." Click create.
c. Paste in between the Sub and End Sub lines.

If this works, you can assign a shortcut key to the macro by clicking
"options" after doing Tools > Macro > Macros...

2. I really don't know why this should happen. Sorry!

jpdphd

 

[Mr. T]

Mr. T,

Sorry for the delay - I have been out of town.

No need to be sorry, I'm just grateful you taking the time to help.

1. Perhaps you have to do the following to get the Personal Macro
Workbook started.
a. View the macro and copy the text in between the Sub line and the
End Sub line.
b. Return to spreadsheet. Tools > Macro > Macros...
enter Windows2Mac into the name box and choose "Personal Macro
Workbook" in the pull down menu for "Macros in..." Click create.
c. Paste in between the Sub and End Sub lines.

If this works, you can assign a shortcut key to the macro by clicking
"options" after doing Tools > Macro > Macros...

(...)

PERFECT!!
Thank you, jpdphd, your thorough guidance saved us for a lot of trouble and
irritation.
Appreciate it very much, thanks again,

 

[jpdphd]

My pleasure! Good luck with the business!