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.

Originally Posted by cytop

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

Originally Posted by cytop

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

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.

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

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

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.