+ Reply to Thread
Results 1 to 10 of 10

MACRO to retain format when expoting as a CSV

  1. #1
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    MACRO to retain format when expoting as a CSV

    I have a macro when export as file as a CSV file


    the file exports perfectly, but the text in Col M changes. I want this to be exactly the same when converting to the CSV file

    Please Login or Register  to view this content.



    Please Login or Register  to view this content.

    it would be appreciated if someone could kindly amend my code
    Attached Files Attached Files
    Last edited by alansidman; 06-18-2019 at 10:57 AM.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this ‼


    According to your attachment a demonstration as a starter :

    PHP Code: 
    Sub Demo()
        
    Dim F%, Rw As Range
            F 
    FreeFile
            Open ThisWorkbook
    .Path Application.PathSeparator "Export .csv" For Output As #F
        
    For Each Rw In Sheet1.UsedRange.Rows
            
    Print #F, Join(Application.Index(Rw.Value, , 0), ",")
        
    Next
            Close 
    #F
    End Sub 
    ► Do you like it ? ► ► So thanks to click on bottom left star icon « Add Reputation » !

  3. #3
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: Hi ! Try this ‼

    Thanks for your help Marc

    The sheet is exported as a csv, but the data in Col M is not exported in the same format as the .xlsm file

    Kindly test and amend

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: MACRO to retain format when expoting as a CSV

    Any problem with this?
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: MACRO to retain format when expoting as a CSV

    Thanks Jindon

    The 1/2/2019 in cell M2 appears as 01/02/2019 on the CSV and should appear as 1/2/2019 in the CSV file i.e the values in Col M in the source workbook must be exactly the same as in the csv file

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: MACRO to retain format when expoting as a CSV

    No, it should return as what you see.

    Open the file with Text Editor like Notepad...
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: MACRO to retain format when expoting as a CSV

    Hi Jindon

    In notepad it displays as 1/2/2019, 1/3/2019, but the csv shows 01/02/2019, 01/03/2019


    I guess that this is the way it is going to be displayed

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: MACRO to retain format when expoting as a CSV

    If you open csv via Excel, it normally doesn't open correctly because Excel's Auto Casting functionality will disturb.

    csv is just a text file and it can be open by Excel, but you should know that it will annoy you.

  9. #9
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,709

    Re: MACRO to retain format when expoting as a CSV

    Thanks for letting me know

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Hi ! Try this ‼

    Quote Originally Posted by Howardc1001 View Post
    Thanks for your help Marc
    The sheet is exported as a csv, but the data in Col M is not exported in the same format as the .xlsm file
    Kindly test and amend
    Already tested as I wrote « according to your attachment » (initial) it well works ‼ So just try with the same attachment
    And your new csv attachment is the proof : exacty the same as your column M !
    'Cause this column is text and not date within these workbooks attachments …
    So this is what happens with a bad attachment not reflecting the real file …

+ 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. [SOLVED] Date formats don't retain their format?
    By RustyNail in forum Excel General
    Replies: 4
    Last Post: 02-13-2019, 05:28 AM
  2. How to retain superscript format?
    By B.W.B. in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-12-2018, 12:41 PM
  3. Function to retain text format
    By reimar_rem in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2018, 03:43 AM
  4. [SOLVED] Retain Format when refering to another Cell
    By hemants in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-20-2010, 11:57 AM
  5. Want to retain text format
    By Baalbek in forum Excel General
    Replies: 10
    Last Post: 06-12-2009, 07:35 AM
  6. Retain color/format from ws1 to ws2
    By Bevbarker in forum Excel General
    Replies: 2
    Last Post: 04-29-2008, 07:33 PM
  7. [SOLVED] Retain date format in cell
    By Jan 2HW in forum Excel General
    Replies: 1
    Last Post: 08-09-2006, 02:34 PM

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