+ Reply to Thread
Results 1 to 5 of 5

CSV in Visual Basic

  1. #1
    Registered User
    Join Date
    08-09-2021
    Location
    Sweden
    MS-Off Ver
    365
    Posts
    3

    Question CSV in Visual Basic

    I have a list in excel which I often export to CSV. It works well, I get a list with ";" as separator and "," as sign for decimals which is the standard in Sweden.
    Then I record a macro doing this task and it works fine. Until I run the macro! Visual Basic uses "," as separator and "." as sign for decimals and this is of course fatal and the file does no work.
    I have learnt that csv is different in US than in Europe.

    Question is:
    How do I make a CSV-file in VB that meets european standards (";" and ",")?

    Marten

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,903

    Re: CSV in Visual Basic

    If you are using the SaveAs method to export it, try adding the Local:=True argument.
    Rory

  3. #3
    Registered User
    Join Date
    08-09-2021
    Location
    Sweden
    MS-Off Ver
    365
    Posts
    3

    Re: CSV in Visual Basic

    Yes I am using the SaveAs method. I added "Local:=True" and it partly solved the problem. Thanks.
    The part it solved is it is using ; and ,. But when I produce a csv-file from excel it is still readable from excel. This new csv-file from VB is not readable by excel anymore i. e. it does not use columns.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent Monthly Channel / Insiders Beta
    Posts
    8,903

    Re: CSV in Visual Basic

    Compare the output in Notepad to a CSV file that does work. What is the difference?

  5. #5
    Registered User
    Join Date
    08-09-2021
    Location
    Sweden
    MS-Off Ver
    365
    Posts
    3

    Re: CSV in Visual Basic

    Here is what I use:

    ActiveWorkbook.SaveAs Filename:=fil, FileFormat:=xlCSV, Local:=True, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False

    This is csv from excel (no values for 2010-2015) and how I want it to look:

    0;2010;2011;2012;2013;2014;2015;2016;2017;2018;2019;2020;2021
    1;;;;;;;36,49;38,29;39,4;36,28;36,64;39,98
    2;;;;;;;36,55;38,95;39,69;36,33;36,09;39,84

    This is csv originally produced by VB:

    0,2010,2011,2012,2013,2014,2015,2016,2017,2018,2019,2020,2021
    1,,,,,,,36.49,38.29,39.4,36.28,36.64,39.98
    2,,,,,,,36.55,38.95,39.69,36.33,36.09,39.84

    This is csv when I add "Local:=True":

    0;2010;2011;2012;2013;2014;2015;2016;2017;2018;2019;2020;2021
    1;;;;;;;36,49;38,29;39,4;36,28;36,64;39,98
    2;;;;;;;36,55;38,95;39,69;36,33;36,09;39,84

    The 1st and 3rd look similar in notepad and works well in excel, but when I use VB to open the 3rd file it is a mess (it doesnt recognize the decimal)! Then I added "Local=True" to the open commando and it works fine!
    Thanks for your input

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Visual basic Help
    By jrortiz2 in forum Excel General
    Replies: 1
    Last Post: 10-27-2010, 04:53 AM
  2. Visual Basic Help
    By romancebao in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-21-2009, 05:42 PM
  3. Get Visual Basic
    By SkeeterDon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-27-2009, 05:10 PM
  4. Visual Basic Help
    By adam2308 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-02-2009, 06:32 PM
  5. Visual Basic changes?
    By LMieth in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-22-2008, 03:36 PM
  6. [SOLVED] Can I run Visual Basic procedure using Excel Visual Basic editor?
    By john.jacobs71 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-26-2005, 10:25 AM
  7. Replies: 1
    Last Post: 09-13-2005, 07:06 AM
  8. [SOLVED] visual basic
    By fredbzr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-08-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1