+ Reply to Thread
Results 1 to 11 of 11

Macro | save as csv but with colums and rows intact

  1. #1
    Registered User
    Join Date
    06-16-2022
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Macro | save as csv but with colums and rows intact

    Hi everyone,

    I'm struggling with this for several weeks now. I hope someone can help me out. I have a feeling it's possible but it's quite challenging.

    For my job I must put article data in Excel file and enrich/adjust it. I have created a Template for this with several macro's. When this is done and it's complete I must upload it in our system. The system can only read macro files. But here is the thing: it only reads CSV's with the data still devided over the columns and rows. I know this is not really where CSV stands for (comma seperated) but there is now other way to read the data in the system. For that I call it "semi convert into CSV file"

    I press after each completed file F12>select CSV divided by list seperation>input title (usually "UPLOAD" or "CSV")>save>then click "Yes".

    For this step i really want to create a macro which does the same thing in the background and creates a (semi)CSV file in the same folder as the original Excel template. The folder of the Excel template is always different so the path must be dynamic. For example: when I have 3 brands to upload I create 3 folders for these brands for a certain season. I put a copy of the empty Excel template in each folder and then I edit and save them individually for importing in the system. The CSV file can stay closed. It would be nice if the original Excel file stays open.

    I already saw a similar thread (I even think it's the only one) but unfortunately it's nog quite the thing I'm looking for.

    Save excel macro file as .csv with date to same folder as original file (VBA)

    BTW each template file contains the same amount and order of columns, via the column titles.

    But vertically each list (range) is different in terms of length

    Could someone please help me out to make this very difficult thing happen? I would be very grateful!

    I anything else is needed, please let me know.

    Thanks in advance.

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    10 ... 21 - 32 & 64
    Posts
    2,840

    Re: Macro | save as csv but with colums and rows intact

    What is a 'semi CSV' file? It's either comma delimited or not. (Or using another delimiter).

    But, for info, you can save a CSV file in the same folder as the workbook simply by using
    Please Login or Register  to view this content.
    Last edited by cytop; 06-23-2022 at 07:27 AM.

  3. #3
    Registered User
    Join Date
    06-16-2022
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Macro | save as csv but with colums and rows intact

    Quote Originally Posted by cytop View Post
    What is a 'semi CSV' file? It's either comma delimited or not. (Or using another delimiter).

    But, for info, you can save a CSV file in the same folder as the workbook simply by using
    Please Login or Register  to view this content.
    Hi,

    Thanks for the reaction. I've tried that already but then I get the normal CSV seperation with the delimited comma. If it's all in one colum the system can't import it.

    By "semi CSV" I mean a csv file but not delimited at all. Just the same as the initial Excel except without the layout of colors etc. I've made up this name myself it's not a real term or something.

    Here is an example what I mean, this is was already saved as CSV with the "normal"way but this is at least 3 mousclicks and searching for the CSV file and the "yes" on the prompt message.


    Knipsel.JPG
    Last edited by Mitch777; 06-23-2022 at 05:06 PM.

  4. #4
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    10 ... 21 - 32 & 64
    Posts
    2,840

    Re: Macro | save as csv but with colums and rows intact

    This has gone from 'vague' to 'very vague' ...

    If you don't want comma separated then what do you want? Literally any character, printable or not, can be used as a separator, including Tabs, which is another very common separator. Another format is 'fixed width' where the first x characters go to make up the first fields, the next y characters the next field and so on.

    This is the elephant in the room:

    Quote Originally Posted by Mitch777 View Post
    ... If it's all in one colum the system can't import it
    If you have 1 or more columns that need to be split then you are going to have to do some pre-processing to get that data into individual columns - a general 'Save as CSV' (or whatever) is not going to do it.

  5. #5
    Registered User
    Join Date
    06-16-2022
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    6
    Quote Originally Posted by cytop View Post
    This has gone from 'vague' to 'very vague' ...

    If you don't want comma separated then what do you want? Literally any character, printable or not, can be used as a separator, including Tabs, which is another very common separator. Another format is 'fixed width' where the first x characters go to make up the first fields, the next y characters the next field and so on.

    This is the elephant in the room:



    If you have 1 or more columns that need to be split then you are going to have to do some pre-processing to get that data into individual columns - a general 'Save as CSV' (or whatever) is not going to do it.

    I understand it's very complicated. But as shown in the picture it's saved as CSV. The real proof of it is that the everything is blanc if I open the CSV file. The headercells are colored in the original Excel file. Besides, it also coverts EAN codes.

    Believe me. If you click save as or F12 then select CSV seperated by seperator en then click Yes all colums are just spread over the sheet.

    It seems if you do it manually like this it still has the samecolums and rows, but if you do it automatically with a macro it really "converts" it to a "real" CSV with everything in 1 column with the chosen seperation sign.

    I'm starting to think this could be bug in Excel. Coincidentally all the companies I worked for can only import the manual generated CSV's in the ERP systems, so with the infor devided over the columns and rows like the original Excel sheet
    Last edited by Mitch777; 06-23-2022 at 02:48 PM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,364

    Re: Macro | save as csv but with colums and rows intact

    Is the file in post #3 a correct file or an incorrect file? .csv files are just text files, what does the same file look like when opened in a text editor (like notepad or wordpad or similar)? What do a correct and an incorrect file look like when opened in a text editor? I think the key to solving this problem is to clearly describe what text elements are necessary in the file, which means seeing the underlying text and not Excel's interpretation of the underlying text.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Registered User
    Join Date
    06-16-2022
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Macro | save as csv but with colums and rows intact

    Wow, I just don't get it. I have tested it with very simple fictional files manually (file "DEBIJ") and with the macro code below (file "YourFileNameHere")

    ActiveWorkbook.SaveAs Filename:= Activeworkbook.path & "\" & "YourFileNameHere.csv", FileFormat:=xlCSVMSDOS, CreateBackup:=False

    Both saved as CSV in the desired folder. If I open it in notepad both have the same seperation with ;
    But when I open the CSV files in Excel (standard program for these type of files) I get different results. DEBIJ is correct how it should be, but the one created with the macro is incorrect, in one column seperated with , and cannot be imported by ERP system.

    Please see images below. Is there a solution for this? In other words: Is it possible to get a macro which have the same results as file "DEBIJ" when opened in Excel?

    NOTEPAD DEBIJ.JPG
    NOTEPAD YOUR FILENAME HERE.JPG
    EXCEL CSV DE BIJ.JPG
    EXCEL CSV YOUR FILE NAME HERE.JPG
    Last edited by Mitch777; 06-23-2022 at 05:08 PM.

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,364

    Re: Macro | save as csv but with colums and rows intact

    files failed to attach. Be sure you are following the steps in the yellow banner when adding attachments.

  9. #9
    Registered User
    Join Date
    06-16-2022
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Macro | save as csv but with colums and rows intact

    Quote Originally Posted by MrShorty View Post
    files failed to attach. Be sure you are following the steps in the yellow banner when adding attachments.
    Thanks, it should be OK now.

    Someone who has a solution for this would be a real magician

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    14,364

    Re: Macro | save as csv but with colums and rows intact

    If I'm reading the pictures correctly, the file DEBIJ (the correct file) is a text/csv file using semicolons ";" as the list/field delimiter and comma "," as the decimal separator. I'm assuming this is consistent with your operating system's language and regional settings.

    The file YourFileNameHere is also a text/csv file using comma "," as the list/field delimiter and period "." as the decimal separator. This seems consistent with US standard settings. This is significant because it is not the first time that I have seen VBA be less internationally aware than Excel.

    This isn't the kind of programming I do, so I am relying completely on MSFT's help file for the SaveAs method (https://docs.microsoft.com/en-us/off...orkbook.saveas ). It appears that there is a "local" parameter for this method that tells Excel whether to use US settings (the default False value for this parameter) or to use your local settings (not default True value). If I understand the help file and the syntax for using named parameters, it should hopefully be as simple as adding the correct value for this Local parameter:
    Please Login or Register  to view this content.
    Perhaps you and/or others who are more knowledgeable about using VBA in non-US settings can test and play around with that information to figure out how to make your VBA code output an internationally aware text/csv file.

  11. #11
    Registered User
    Join Date
    06-16-2022
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    6

    Re: Macro | save as csv but with colums and rows intact

    Quote Originally Posted by MrShorty View Post
    If I'm reading the pictures correctly, the file DEBIJ (the correct file) is a text/csv file using semicolons ";" as the list/field delimiter and comma "," as the decimal separator. I'm assuming this is consistent with your operating system's language and regional settings.

    The file YourFileNameHere is also a text/csv file using comma "," as the list/field delimiter and period "." as the decimal separator. This seems consistent with US standard settings. This is significant because it is not the first time that I have seen VBA be less internationally aware than Excel.

    This isn't the kind of programming I do, so I am relying completely on MSFT's help file for the SaveAs method. It appears that there is a "local" parameter for this method that tells Excel whether to use US settings (the default False value for this parameter) or to use your local settings (not default True value). If I understand the help file and the syntax for using named parameters, it should hopefully be as simple as adding the correct value for this Local parameter:
    Please Login or Register  to view this content.
    Perhaps you and/or others who are more knowledgeable about using VBA in non-US settings can test and play around with that information to figure out how to make your VBA code output an internationally aware text/csv file.

    You're THE MAN!! "Local:=True" was the key to succes! This is what I meant and how it should be! I will do the ultimate test tomorrow at work, but I'm assuming the ERP system can read it correctly now as the data in the csv remains devided by columns and rows. It was "just" the regional settings. I think when you click "yes" at the prompt screen when saving it manually it automatically uses the non US settings, European? settings I guess.

    Anyhow, MrShorty & cytop thanks a million you guys. I'm very grateful and happy. If I can do something in return please let me know.

    I will stick around at this forum, this is really great.

    Have a nice day/evening!

+ 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. filtering with the rows intact
    By veloopity in forum Excel General
    Replies: 3
    Last Post: 09-29-2014, 06:27 AM
  2. Macro to compare two colums and copy rows if data matches.
    By avinash.aswani in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-11-2013, 03:03 AM
  3. [SOLVED] Save as with new name - but keep old file intact - with original name
    By ILoveStMartin in forum Excel General
    Replies: 1
    Last Post: 05-07-2013, 07:38 PM
  4. Macro To Copy Data And Shift Rows Up Of Multiple Colums
    By kany in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-02-2013, 04:21 AM
  5. Keep rows intact while sorting
    By templedentist in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-22-2013, 08:00 PM
  6. Macro to Transpose multiple rows to one colums
    By Karina in forum Excel General
    Replies: 2
    Last Post: 08-27-2011, 12:34 AM
  7. macro total for colums with changing # of rows
    By BillyRogers in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-26-2006, 05:00 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