VBA Excel export the file as .csv with keeping data in the proper columns

Question

I want to have the .csv file saved with the columns as they look in my mother's workbook.

I used the following code:

Application.DisplayAlerts = False

     For Each Wksht In ActiveWorkbook.Worksheets


      myPath = ThisWorkbook.Path & "\"

       ActiveWorkbook.Sheets(Wksht.Index).Copy
       ActiveWorkbook.SaveAs Filename:=myPath & "Address list - NEW", FileFormat:=xlCSV, 
       CreateBackup:=False
       ActiveWorkbook.Close

       Next Wksht

       Application.DisplayAlerts = True

but instead of clear data separated by columns with their title headers, I am getting everything messy in one column.

What am I missing here?

The csv file has been correctly saved. I mean comma separated. Your Excel does not open it as it should be. Try opening it as text, please. And use comma separator... — FaneDuru
– FaneDuru, Commented Jan 31, 2021 at 11:07
Happens to me all the time. My system list separator is a semi-colon (;) while the csv file gets saved with commas (,). — VBasic2008
– VBasic2008, Commented Jan 31, 2021 at 11:12
@FaneDuru: Thanks. That did that trick, but how do I do it from VBA? (Note, I'm not OP) — VBasic2008
– VBasic2008, Commented Jan 31, 2021 at 11:19
@VBasic2008: I posted a piece of code which I think will do the trick. But, not tested... My system uses comma as list separator and I am a little busy to change it for testing reason only. A second solution can be to use TextToColumns after opening it as it Excel default behaves. — FaneDuru
– FaneDuru, Commented Jan 31, 2021 at 11:26

FaneDuru · Accepted Answer · 2021-01-31 11:47:13Z

3

Try the next way of opening, please (@VBasic2008, too):

Workbooks.OpenText Filename:=myPath & "Address list - NEW", Origin _
        :=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , Tab:=False, Comma:=True

If it does not work, please test the solution secommended by VBasic2008:

Workbooks.Open Filename:=myPath & "Address list - NEW", Format:=2

If the above solutions do not work (not tested), or even if they work but need an alternative, try the next way to split the resulted (one column) opening:

Private Sub testTestTextToColumns()
  Dim i As Long, sh As Worksheet, rng As Range
  Set sh = ActiveSheet
  
    Set rng = sh.Range("A:A")
    rng.TextToColumns Destination:=sh.Range("A:C"), Comma:=True, FieldInfo:=Array(Array(1, 2), Array(1, 2), Array(1, 2))
End Sub

Note: The second code should process the default opening result. What you obtain now when try opening the csv...

edited Jan 31, 2021 at 11:47

answered Jan 31, 2021 at 11:24

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

10 Comments

VBasic2008 Over a year ago

I just figured out that it works with Workbooks.Open Filename:=FilePath, Format:=2 for commas in the file, but it doesn't work for semicolons (Format:=4). Test it and add it to your answer (you do have semicolons as the list separator?).

FaneDuru Over a year ago

@VBasic2008: No, I have comma like list separator... That's why I did not test my code... Did you also try the TextToColumns processing (after the standard inconvenient opening)? I suppose it should be beyond the list separator type. Can you confirm that? I added your suggestion, but I cannot really testing it... In my case, it will anyhow open correctly.

VBasic2008 Over a year ago

TextToColumn works fine, but it's kind of ridiculous to use it when opening a file. I have tested your code and it works. I will look into OpenText a little deeper (never heard of it or forgotten about it) and come back to you a little later.

Geographos Over a year ago

Hi All, thanks for help. The first solution was tested and worked. Really helpful :)

FaneDuru Over a year ago

@MKR: Glad I could help! But should not be bad to test the others, I think. It is not bad to know what it may function when needed... :)

|

Collectives™ on Stack Overflow

VBA Excel export the file as .csv with keeping data in the proper columns

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related