Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Dec 2009
    Location
    Suffolk, UK
    Posts
    41
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel - output in Unicode

    I am using Excel 2000 on my desktop to manage my GPS waypoints, as I can't find a better program.

    To use GPSBabel and convert to .gpx and .gdb formats, I use one Batch file (.bat) to convert all files in one folder.

    However GPSBabel does not work with Excel .xls files, so I have to load and save every .xls file into Excel 2000 and save as a .csv file.

    Unfortunately, in Europe, many characters are accented and they get lost going into a .csv file from Excel 2000. So I load the .csv file into WordPad and save as a Unicode .txt file. Then I run my Batch file as above.

    Is it possible to get Excel 2000 to save a .csv file in Unicode?

    I've just bought a new laptop with the current Excel on it. Will it be suitable to resolve my problem?

    In an ideal world, I'd like to be able to run just a single Batch file to convert all .xls files to .csv in Unicode. I could do this in the days of DOS, but is it possible with Windows - XP on my desktop; W7 on my laptop.

    I'm even prepared to purchase 'Microsoft Office Home and Student 2010' to replace Excel 2000 on my desktop if that would help.

    Thanks,

    Reg

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    You can get this to work, to an extent anyway, as follows:

    When you Save As, select Unicode Text, and when you enter the filename to Save As also manually append the extension .csv.

    Play around with that idea to get the result you want - it does retain the full Unicode characters in a form of deliminated file.

  3. #3
    Lounger
    Join Date
    Dec 2009
    Location
    Suffolk, UK
    Posts
    41
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks.

    That retains the accented characters, but replaces the comma with a tab as field delimiter. I can easily amend my batch file to use a tab instead of a comma.

    Now to find a way to open a .xls file in Excel and then save in the Unicode form, automatically.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    This code works for me:

    Code:
    ActiveWorkbook.SaveAs Filename:= "C:\. . . .\My Documents\Test.csv", FileFormat:= xlUnicodeText, CreateBackup:=False
    So you can embed it is Startup Macro, which goes on to close down the file !

  5. #5
    Lounger
    Join Date
    Dec 2009
    Location
    Suffolk, UK
    Posts
    41
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks.

    I've only just started to investigate macro code within Excel as I've not needed it before. I got a semi-automatic open and save as to work in the global macro, but it used the filename of my sample .xls file. Having a similar macro in every .xls file would solve that.

    However I've since found 'XLS2CSV Converter' <http://xls2csv.genxcrowd.com/>

    It has a batch feature and (not yet investigated) a CLI capability. So I may be able to achieve my goal of converting all .xls files to .gpx and .gdb with a single Batch file.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •