Code does not save .csv file properly

Question

I have a excel workbook that i want to save as a csv file. This is the code i use:

Application.DisplayAlerts = True

ActiveWorkbook.SaveAs FileName:= _
"C:\Users\Geoffrey\Dropbox\DCS PROGRAM\FILES\1. QUOTES\3. PROGRAM FILES\DOOR QT REQUESTS\DOORS_COMBINED.csv", FileFormat:=xlCSV _
, CreateBackup:=False


Workbooks.Open ("C:\Users\Geoffrey\Dropbox\DCS PROGRAM\FILES\1. QUOTES\3. PROGRAM FILES\DOOR QT REQUESTS\DOOR PROGRAM.xlsm")

Workbooks("DOORS_COMBINED.csv").Close

The problem is when i save it it does not look the same as the excel workbook.

Excel file:

  A        B      C       D
Item 1  Item 2  Item3   Item 4
Item 1  Item 2  Item3   Item 4
Item 1  Item 2  Item3   Item 4

.CSV File: (Everything gets combined into column A)

          A                    B        C       D
Item 1Item 2Item3Item 4
Item 1Item 2Item3Item 4
Item 1Item 2Item3Item 4

If i save this manually as a .csv it turns out correct.

I think this could be the problem. I set excel to read the delimeter as ; because my descriptions contain commas.

Notepad view when using VBA to SaveAs:

Notepad view when SaveAs manually:

Does Excel file actually contain commas between columns as you show? Also , you are saving the ActiveWorkbook as csv, then continue on with additional programming. Lines after are done in a csv file. When you exit, macros are lost. You might have conflict. Try saving to csv and exiting right after. Also might consider saving ActiveSheet. — Parfait
– Parfait, Commented Nov 14, 2015 at 20:38
I'm not sure I follow. Your code shows you saving DOORS_COMBINED.csv and then opening DOOR PROGRAM.xlsm. So when you're opening the file you're unhappy with, are you opening it manually? Does it prompt you to specify how the file is delimited by chance? — Marc
– Marc, Commented Nov 14, 2015 at 20:54
The excel file doesn't actually contain commas. I tried running the code i have by commenting out the open and leaving the close workbook in and it still does whats above. I tried commenting both the open and close, and this made me close the new saved as file manually. This prompts me if i wanna save and everything and it then saves the file correctly. This is what I'm trying to get around. I want the user to just click the button and have it save correctly — Geoff
– Geoff, Commented Nov 14, 2015 at 20:59
@Marc I reopen PROGRAM.xlsm because my macros are in there and i need to run a few more operations after i save as. I tried a few things and was having conflicts with files being opened and closed. Was easier just to seperate the save as .csv. Maybe wont ave to do that after i figure this out. Yes I'm opening it the output file manually because I'm checking to see if it save right. I have another button that does stuff to it after this. When saving it does not ask what delimiter to use. Let me know if i can clarify anything else. — Geoff
– Geoff, Commented Nov 14, 2015 at 21:05

codersl · Accepted Answer · 2015-11-18 02:39:41Z

1

I think the solution has been alluded to multiple times but to be clear.

This works for me:

ActiveWorkbook.SaveAs Filename:="C:\Temp\test_out.csv", FileFormat:=xlCSV, CreateBackup:=False, local:=True

The trick is the local:=True and making sure that your regional settings List separator is a semicolon.

To set the List separator, open Control Panel => Region and Language, click on Additional settings and change List separator: to ;

answered Nov 18, 2015 at 2:39

codersl

2,3425 gold badges32 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Geoff Over a year ago

Thanks @codersl I dunno what i did before but i must have been in a hurry. Thanks for all the help! Works like a charm!

Oskar Shon · Accepted Answer · 2015-11-15 15:46:32Z

0

You can try this format

FileFormat:=xlCSVMSDOS

and try to add after CreateBackup:=False this line

, Local:=True

answered Nov 15, 2015 at 15:46

Oskar Shon

427 bronze badges

1 Comment

Geoff Over a year ago

Just tried this and it doesnt work. It saves it as a blank .csv file. I had my finger crossed =) @Oskar Shon Thanks

Collectives™ on Stack Overflow

Code does not save .csv file properly

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related